How Can I reduce my transacion log file?

I Have a Database with a transaction log file 31 Gb, and I have problem to
backup it.
I want decresea it but don't remember How.
LorenaHi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model before doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateon
ly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Lorena" <Lorena@.discussions.microsoft.com> wrote in message
news:6B7189C4-2904-4000-BD28-1A89C8BA9866@.microsoft.com...
> I Have a Database with a transaction log file 31 Gb, and I have problem
to backup it.
> I want decresea it but don't remember How.
> Lorena

How Can I reduce my transacion log file?

I Have a Database with a transaction log file 31 Gb, and I have problem to backup it.
I want decresea it but don't remember How.
Lorena
Hi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model before doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateonly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Lorena" <Lorena@.discussions.microsoft.com> wrote in message
news:6B7189C4-2904-4000-BD28-1A89C8BA9866@.microsoft.com...
> I Have a Database with a transaction log file 31 Gb, and I have problem
to backup it.
> I want decresea it but don't remember How.
> Lorena

how can i read the log file content in enterprise maanger?

how can i read the log file content in enterprise maanger?Hi
I am affraid you cannot.
Some of the options
DBCC LOGINFO (7) --database id
or
Visit at http://www.lumigent.com/
"Inamori" <test@.test.com> wrote in message
news:e3DyCi2rEHA.1272@.TK2MSFTNGP09.phx.gbl...
>
|||However you could use the tool from Lumigent ( Log Explorer )
www.lumigent.com
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
"Inamori" <test@.test.com> wrote in message
news:e3DyCi2rEHA.1272@.TK2MSFTNGP09.phx.gbl...
>

how can i read the log file content in enterprise maanger?

how can i read the log file content in enterprise maanger?Hi
I am affraid you cannot.
Some of the options
DBCC LOGINFO (7) --database id
or
Visit at http://www.lumigent.com/
"Inamori" <test@.test.com> wrote in message
news:e3DyCi2rEHA.1272@.TK2MSFTNGP09.phx.gbl...
>|||However you could use the tool from Lumigent ( Log Explorer )
www.lumigent.com
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
"Inamori" <test@.test.com> wrote in message
news:e3DyCi2rEHA.1272@.TK2MSFTNGP09.phx.gbl...
>

how can i read the log file content in enterprise maanger?

how can i read the log file content in enterprise maanger?Hi
I am affraid you cannot.
Some of the options
DBCC LOGINFO (7) --database id
or
Visit at http://www.lumigent.com/
"Inamori" <test@.test.com> wrote in message
news:e3DyCi2rEHA.1272@.TK2MSFTNGP09.phx.gbl...
>|||However you could use the tool from Lumigent ( Log Explorer )
www.lumigent.com
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
"Inamori" <test@.test.com> wrote in message
news:e3DyCi2rEHA.1272@.TK2MSFTNGP09.phx.gbl...
>sql

How can I read the content of transaction log

How can I read the content of transaction log?How can I read the content of transaction log?
Use DBCC LOG to view Transaction log...

DBCC LOG

This command is used to view the transaction log for the specified database.

Syntax:

DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])

where:

dbid or dbname - Enter either the dbid or the name of the database
type - is the type of output, and includes these options:

0 - minimum information (operation, context, transaction id)

1 - more information (plus flags, tags, row length, description)

2 - very detailed information (plus object name, index name, page id, slot id)

3 - full information about each operation

4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID

by default, type = 0

To view the transaction log for the master database, run the following command:
DBCC log (master)

to understand transaction log see this ...Link (http://www.windowsitlibrary.com/Content/77/21/1.html)|||Thanks :-)

How can I programmatically tell if agents are running?

Ideally I am looking for a procedure that I can call in T-SQL to verify that the Log Reader, Snapshot, and Sql Agents are all up and running on a given server. I found the undocumented sp_MSrepl_distributionagentstatussummary proc and got it to work for the distribution agent. I also found sp_MSrepl_agentstatusummary, but I've yet to get this to work. Overall, I'd really like to have a better way to do this.

This absolutely must be something callable from the sqlcmd interface. It can not be through a UI nor anthing more complicated than standard T-SQL.

Any suggestions?Query the status column in the history tables in the distribution database. there should be MSsnapshot_history, MSlogreader_history, MSdistribution_history.|||

Thanks for your response Greg.

I've spent some time looking at these tables and can't quite figure how to determine if one of the agents are down or not. I've induced some failures, took some of the agents offline, and generally tried to muck up the environment but couldn't find a reliable way to indicate if any of the darned things were actually running. I suppose I can look for the max timestamp of a 'started' process that doesn't have a 'error' or 'completed' record whose timestamp is more recent, but that doesn't feel to be a reliable method.

|||

Books Online explains each column for each table, as well as the various values for column runstatus.

For example, MSdistribution_history (this is cut/paste from books online):

runstatus

int

The Running status:

1 = Start.

2 = Succeed.

3 = In progress.

4 = Idle.

5 = Retry.

6 = Fail.

|||

Yup, this is a great example of what I was saying about the table not being a reliable indicator of the actual status of the agent. When I manually stopped the Sql Agent, I noticed rows in MSdistribution_history that looked like the following:

agent_id runstatus start_time time duration comments

5 2 <time> <time> 88854 The process was successfully stopped.

It seems that the runstatus 'Success' really means stopped in this case. It was immediately followed by a 'Starting agent' row when I turned it back on again. This led me to wonder if there are any other conditions I'd have to look for in order to see if things are enabled.

Maybe that's the confusion here... I don't care if a particular job is running, just that the agent is started / enabled / turned on / in a state where it can be used, etc. I know I can start looking for the most recent status log that indicates the agent is operable, but I've been in this business too long not to be lazy and just look for a darned enabled / disabled flag.

|||

Replication agents can be invoked numerous ways - from command line, ActiveX, or SQL jobs. The history tables will give you the status of the agent at its current state. For example, if your agent is set to run in continuous mode, and status = 3, then all is good. If status = 6, then your agent has failed. If your agent is scheduled to run at regular intervals, and you query it one hour later, and status = 2, that means the agent job ran successful. IF it's 6, then it failed. If it's 3, then the agent is still running.

Now, how the agent is invoked is totally up to you. HOw you determine if it's in a state where it can be used, well, I'm not sure how to define that for you. YOu need to be more specific with what you're trying to accomplish. Otherwise the replication history tables will give you the status of the current/last agent run.

How can i output result to a log file?

Hi all,
In case i have a script file containt tables, functions, ... when i use Query Analyzer to run this file, the result output in a window. Now i want this result output to a file named logfile.txt. How can i do that?
Thanks first.qa has this capability. Options -> Results -> Defaul results to: FILE.

if you want to do it from command line, you can use OSQL. See book online for details.

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanks
Hi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml web
> service and query a functions for data that i can insert into a table ?
> thanks
>
>
|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanksHi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml we
b
> service and query a functions for data that i can insert into a table ?
> thanks
>
>|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:
>

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanks
Hi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml web
> service and query a functions for data that i can insert into a table ?
> thanks
>
>
|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanksHi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:

> Hello there
> I want to know if there is a way I can make a DTS package log to an xml we
b
> service and query a functions for data that i can insert into a table ?
> thanks
>
>|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:
>

How can I make a DTS get data from an XML web services

Hello there
I want to know if there is a way I can make a DTS package log to an xml web
service and query a functions for data that i can insert into a table ?
thanksHi
You may be able to use the sp_OA* procedures to do this.
John
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way I can make a DTS package log to an xml web
> service and query a functions for data that i can insert into a table ?
> thanks
>
>|||Also... you could use an activeX task and set a global variable.
John
"John Bell" wrote:
> Hi
> You may be able to use the sp_OA* procedures to do this.
> John
> "Simo Sentissi" wrote:
> > Hello there
> >
> > I want to know if there is a way I can make a DTS package log to an xml web
> > service and query a functions for data that i can insert into a table ?
> >
> > thanks
> >
> >
> >

How Can I Look Up Database Log

In either SQL 2000 or SQL 2005.
we can set both Data and Log file's position or initial size or
autogrowth.
I know data are stored in the Data File.
but how do I know what are stored in the Log file?
so
what are stored in the log file in SQL Server's database?
Thanks
Benny wrote:

> In either SQL 2000 or SQL 2005.
> we can set both Data and Log file's position or initial size or
> autogrowth.
> I know data are stored in the Data File.
> but how do I know what are stored in the Log file?
> so
> what are stored in the log file in SQL Server's database?
> Thanks
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Regards
Amish Shah
|||There are some ways out of the box that can reveal info:
select * from ::fn_dblog(null,null)
dbcc log(Pub1, type=4)
However to get hold of the actual TSQL statements you'll need a 3rd-party
tool eg Lumigent Log Explorer
(http://www.lumigent.com/products/le_sql.html). BTW it's true that you could
use transactional replication and sp_browsereplcmds as a means of achieving
this aim without buying a 3rd-party tool, but it's unlikely anyone would
want the unnecessary overhead to go down this route, and the info obtained
is more limited than using Log Explorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How Can I Look Up Database Log

In either SQL 2000 or SQL 2005.
we can set both Data and Log file's position or initial size or
autogrowth.
I know data are stored in the Data File.
but how do I know what are stored in the Log file?
so
what are stored in the log file in SQL Server's database?
ThanksBenny wrote:

> In either SQL 2000 or SQL 2005.
> we can set both Data and Log file's position or initial size or
> autogrowth.
> I know data are stored in the Data File.
> but how do I know what are stored in the Log file?
> so
> what are stored in the log file in SQL Server's database?
> Thanks
http://sqlserver2000.databases.aspf...
log-files.html
Regards
Amish Shah|||There are some ways out of the box that can reveal info:
select * from ::fn_dblog(null,null)
dbcc log(Pub1, type=4)
However to get hold of the actual TSQL statements you'll need a 3rd-party
tool eg Lumigent Log Explorer
(http://www.lumigent.com/products/le_sql.html). BTW it's true that you could
use transactional replication and sp_browsereplcmds as a means of achieving
this aim without buying a 3rd-party tool, but it's unlikely anyone would
want the unnecessary overhead to go down this route, and the info obtained
is more limited than using Log Explorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How Can I Look Up Database Log

In either SQL 2000 or SQL 2005.
we can set both Data and Log file's position or initial size or
autogrowth.
I know data are stored in the Data File.
but how do I know what are stored in the Log file?
so
what are stored in the log file in SQL Server's database?
ThanksBenny wrote:
> In either SQL 2000 or SQL 2005.
> we can set both Data and Log file's position or initial size or
> autogrowth.
> I know data are stored in the Data File.
> but how do I know what are stored in the Log file?
> so
> what are stored in the log file in SQL Server's database?
> Thanks
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Regards
Amish Shah|||There are some ways out of the box that can reveal info:
select * from ::fn_dblog(null,null)
dbcc log(Pub1, type=4)
However to get hold of the actual TSQL statements you'll need a 3rd-party
tool eg Lumigent Log Explorer
(http://www.lumigent.com/products/le_sql.html). BTW it's true that you could
use transactional replication and sp_browsereplcmds as a means of achieving
this aim without buying a 3rd-party tool, but it's unlikely anyone would
want the unnecessary overhead to go down this route, and the info obtained
is more limited than using Log Explorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how can I log 'hackers' failed attempts to login w/ip

Hello, I have a db server that has to be open to the internet.. and I
get 'hackers' trying to login to sa/power all day.
The thing is, the connection is 'force encryption' , so I cannot sniff
the traffic.. and since these 'hackers' are using their own tools, I
cannot tell their client software to send me their IP. (I know i
canget their hostname via C2 audits)
So basically, how do I monitor for these things and how to auto block
IPs.
any ideas?
thanks
Leee
Hi
As well as changing the hostname they also probably falsifying their IP
address. If you really have to leave the database open to the internet, then
you may want to block unknow IP addresses at the firewall. You should also
make sure that you are not using the default ports.
Depending on what you are doing, you may want to consider using a web
service or some other interim application as the means to communicate with
your database, you can then keep a tighter control over who/what connects.
John
"trend" <trend42@.hotmail-dot-com.no-spam.invalid> wrote in message
news:Ru-dncm8YPCz93HfRVn_vA@.giganews.com...
> Hello, I have a db server that has to be open to the internet.. and I
> get 'hackers' trying to login to sa/power all day.
> The thing is, the connection is 'force encryption' , so I cannot sniff
> the traffic.. and since these 'hackers' are using their own tools, I
> cannot tell their client software to send me their IP. (I know i
> canget their hostname via C2 audits)
>
> So basically, how do I monitor for these things and how to auto block
> IPs.
>
> any ideas?
>
> thanks
> Leee
>

how can I log 'hackers' failed attempts to login w/ip

Hello, I have a db server that has to be open to the internet.. and I
get 'hackers' trying to login to sa/power all day.
The thing is, the connection is 'force encryption' , so I cannot sniff
the traffic.. and since these 'hackers' are using their own tools, I
cannot tell their client software to send me their IP. (I know i
canget their hostname via C2 audits)
So basically, how do I monitor for these things and how to auto block
IPs.
any ideas?
thanks
LeeeHi
As well as changing the hostname they also probably falsifying their IP
address. If you really have to leave the database open to the internet, then
you may want to block unknow IP addresses at the firewall. You should also
make sure that you are not using the default ports.
Depending on what you are doing, you may want to consider using a web
service or some other interim application as the means to communicate with
your database, you can then keep a tighter control over who/what connects.
John
"trend" <trend42@.hotmail-dot-com.no-spam.invalid> wrote in message
news:Ru-dncm8YPCz93HfRVn_vA@.giganews.com...
> Hello, I have a db server that has to be open to the internet.. and I
> get 'hackers' trying to login to sa/power all day.
> The thing is, the connection is 'force encryption' , so I cannot sniff
> the traffic.. and since these 'hackers' are using their own tools, I
> cannot tell their client software to send me their IP. (I know i
> canget their hostname via C2 audits)
>
> So basically, how do I monitor for these things and how to auto block
> IPs.
>
> any ideas?
>
> thanks
> Leee
>

how can I log 'hackers' failed attempts to login w/ip

Hello, I have a db server that has to be open to the internet.. and
get 'hackers' trying to login to sa/power all day
The thing is, the connection is 'force encryption' , so I cannot snif
the traffic.. and since these 'hackers' are using their own tools,
cannot tell their client software to send me their IP. (I know
canget their hostname via C2 audits
So basically, how do I monitor for these things and how to auto bloc
IPs
any ideas
thank
LeeHi
As well as changing the hostname they also probably falsifying their IP
address. If you really have to leave the database open to the internet, then
you may want to block unknow IP addresses at the firewall. You should also
make sure that you are not using the default ports.
Depending on what you are doing, you may want to consider using a web
service or some other interim application as the means to communicate with
your database, you can then keep a tighter control over who/what connects.
John
"trend" <trend42@.hotmail-dot-com.no-spam.invalid> wrote in message
news:Ru-dncm8YPCz93HfRVn_vA@.giganews.com...
> Hello, I have a db server that has to be open to the internet.. and I
> get 'hackers' trying to login to sa/power all day.
> The thing is, the connection is 'force encryption' , so I cannot sniff
> the traffic.. and since these 'hackers' are using their own tools, I
> cannot tell their client software to send me their IP. (I know i
> canget their hostname via C2 audits)
>
> So basically, how do I monitor for these things and how to auto block
> IPs.
>
> any ideas?
>
> thanks
> Leee
>

How can I log DDL?

Hallo,
in Oracle I use DDL-Trigger. But how can I document/triggering DDL (for instance "alter table") in Microsoft SQL Server?
Regards, Doreenwhich version of SQL server are you using? DDL triggers are introduced in SQL Server 2005.|||I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables.
Is there a solution for the version 2000?