Log parser

Can someone send me the command to import the application and system log of
the eventlogs into SQL Server for say 2 different servers ?
Does the command have to run every few mins ? How does it handle
duplicates,etc. ? I would like to have the logs in the database no later
than say 15 mins from the time they make it in the respective log. But I
dont know how log parser is smart enough to do that unless I run it every 5
mins but then again, what does it scan for and only ensures it does not
insert duplicates
Thanks
You have to write something custom to do this. You can do it with an SSIS
WMI data reader task. The class to look at is win32_ntlogevent.
Other options include powershell to from xp_cmdshell or a CLR function to
get to WMI.
Jason Massie
http://statisticsio.com
"Hassan" <hassan@.test.com> wrote in message
news:ut0pOZvKIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Can someone send me the command to import the application and system log
> of the eventlogs into SQL Server for say 2 different servers ?
> Does the command have to run every few mins ? How does it handle
> duplicates,etc. ? I would like to have the logs in the database no later
> than say 15 mins from the time they make it in the respective log. But I
> dont know how log parser is smart enough to do that unless I run it every
> 5 mins but then again, what does it scan for and only ensures it does not
> insert duplicates
> Thanks
>

Log is full?

What would be the best way to have the database run the following command when the logfile reaches a certain size?
BACKUP LOG kingjohnor WITH TRUNCATE_ONLY
GO
USE kingjohnor
DBCC SHRINKFILE(kingjohnor_log,1)
Thanks in advance for help : )If you are not going to back uo the transaction log file, then why not change your recovery mode to simple? This way the trans log will not grow at all - it will only hold uncommitted transactions.

log grows after shrinking

I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:

> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.
|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas

"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:

> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.

log grows after shrinking

I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.sql

LOG File Maintenance

Hello! Hope everybody is doing OK.
I have a quick question. I use to run the command below to keep the Log
File of my database small.
BACKUP LOG FlexSol WITH NO_LOG
DBCC SHRINKDATABASE (FlexSol, 10)
The question is, now that I setup replication in this database, would I be
able to run this command without affecting the way replication works, if so,
then what are the steps to perform maintenance in the log file.
Thank you in advance for any input.
Marlene A. Roman
if the log reader has not read and commited transactions in the tlog to the distribution database, you will not be able to truncate this portion of the log.
So, truncate away, as replication will not be affected.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||(...and if the replication type is merge or snapshot, this will not be affected either).
Regards,
Paul Ibison
|||Thank you! :P
|||Paul, you are starting to making me look bad, and I don't need your help for
that
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8EB60E9B-6EA6-48B3-A51E-8D60B858A60B@.microsoft.com...
> (...and if the replication type is merge or snapshot, this will not be
affected either).
> Regards,
> Paul Ibison

log file in USE

I am using the following command but it gave me an error that the log file
is in Use
alter database Aspstate set RESTRICTED_USER with ROLLBACK IMMEDIATE
Go
DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
Go
any idea what I have to do?
Thanks
Noor
Hi,
Can you take the transaction log backup and try shrinking the transaction
log file.
Backup log <dbname > to disk='d:\backup\dbname.trn'
After that try shrinking the file using the comand:-
DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:exrHGdWdEHA.1648@.TK2MSFTNGP11.phx.gbl...
> I am using the following command but it gave me an error that the log file
> is in Use
> alter database Aspstate set RESTRICTED_USER with ROLLBACK IMMEDIATE
> Go
> DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
> Go
> any idea what I have to do?
> Thanks
> Noor
>
|||Thanks HARI
Noor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ert9hRYdEHA.3988@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> Can you take the transaction log backup and try shrinking the transaction
> log file.
> Backup log <dbname > to disk='d:\backup\dbname.trn'
> After that try shrinking the file using the comand:-
> DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
> Thanks
> Hari
> MCDBA
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:exrHGdWdEHA.1648@.TK2MSFTNGP11.phx.gbl...
file
>

Log file for database is full

hello
when i execute this command:
DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
PK_OV_PM_LPolGroupAssignment)
GO
give me this error:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'openview' is full. Back up the transaction log
for the database to free up some log space.
The statement has been terminated.
How can fix this? or what can i do ?
Regards
José Júlio DuarteHi
Every operation in the database is logged to t -log file
Set recovery mode of the database to SIMPLE and run this commnad again
"Jos? J?lio Duarte" <JosJlioDuarte@.discussions.microsoft.com> wrote in
message news:73E755C8-ECC5-43C2-A38C-400A68040BBD@.microsoft.com...
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> Regards
> Jos? J?lio Duarte|||This is a multi-part message in MIME format.
--090303040507050906090408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
José Júlio Duarte wrote:
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> Regards
> José Júlio Duarte
>
You could either:
1: Backup the logfile to truncate the file so the space can be re-used.
(Look up BACKUP LOG in Books On Line)
2: Set the Autogrow ON for the logfile. (Look up ALTER DATABASE in Books
On Line )
3: Manually increase the logfile size. (Look up ALTER DATABASE in Books
On Line)
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--090303040507050906090408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
José Júlio Duarte wrote:
<blockquote cite="mid73E755C8-ECC5-43C2-A38C-400A68040BBD@.microsoft.com"
type="cite">
<pre wrap="">hello
when i execute this command:
DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
PK_OV_PM_LPolGroupAssignment)
GO
give me this error:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'openview' is full. Back up the transaction log
for the database to free up some log space.
The statement has been terminated.
How can fix this? or what can i do ?
Regards
José Júlio Duarte
</pre>
</blockquote>
<font size="-1"><font face="Arial">You could either:<br>
<br>
1: Backup the logfile to truncate the file so the space can be re-used.
(Look up BACKUP LOG in Books On Line)<br>
2: Set the Autogrow ON for the logfile. (Look up ALTER DATABASE in
Books On Line )<br>
3: Manually increase the logfile size. (Look up ALTER DATABASE in Books
On Line)<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--090303040507050906090408--|||You can use DBCC SHRINKFILE (logical filename ... logsize)
to physically shrink the file for want of disk space
M A Srinivas
Jos=E9 J=FAlio Duarte wrote:
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> > Regards
> > Jos=E9 J=FAlio Duarte|||masri999@.gmail.com wrote:
> You can use DBCC SHRINKFILE (logical filename ... logsize)
> to physically shrink the file for want of disk space
> M A Srinivas
>
I doubt that will make any difference? If SQL server reports that the
logfile is full, then there're no free space in the file meaning that
you can't shrink it.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||The first obvious thing to try would be to backup the log file as it states
in the error message. If there still isn't enough log space after backing
up the log you will need to expand the size of the log file somehow - either
free up some space or add another file to the log
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"José Júlio Duarte" <JosJlioDuarte@.discussions.microsoft.com> wrote in
message news:73E755C8-ECC5-43C2-A38C-400A68040BBD@.microsoft.com...
> hello
> when i execute this command:
> DBCC DBREINDEX ('dbo.OV_PM_LPolGroupAssignment',
> PK_OV_PM_LPolGroupAssignment)
> GO
> give me this error:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'openview' is full. Back up the transaction log
> for the database to free up some log space.
> The statement has been terminated.
> How can fix this? or what can i do ?
> Regards
> José Júlio Duarte

LOG Device Corrupted

Hi,
I found my database (Log Device) corrupted, and I used the command
sp_attach_single_file_db to attach the DB device to new sql server. But the
server responed error:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database XXXX, CREATE DATABASE is aborted.
Device activation error. THe physical file name XXXX.LDF may be incorrect.
I tested use another DB to detach and delete the log file, than attach it
again. It is work and the new log file will be created.
What is the problem of my DB device? Is it corrupted as well. Any other
solution to attach the DB device to SQL server?
Please help!!!
Thanks,
HenryHi,
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use the procedure
sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILDLOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
news:eEjj3xUKEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> I found my database (Log Device) corrupted, and I used the command
> sp_attach_single_file_db to attach the DB device to new sql server. But
the
> server responed error:
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database XXXX, CREATE DATABASE is aborted.
> Device activation error. THe physical file name XXXX.LDF may be incorrect.
> I tested use another DB to detach and delete the log file, than attach it
> again. It is work and the new log file will be created.
> What is the problem of my DB device? Is it corrupted as well. Any other
> solution to attach the DB device to SQL server?
> Please help!!!
> Thanks,
> Henry
>|||Hi Hari,
Thanks but in step 7, system prompted error as:
Server: Msg 2526, Level 16, State 2, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC
syntax and options.
I tried in SQL7 ans SQL2000, the result is same. Any setting I've to change
to use this command?
Thanks,
Henry
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGLeRvVKEHA.556@.TK2MSFTNGP10.phx.gbl...
Hi,
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use the procedure
sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILDLOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
news:eEjj3xUKEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> I found my database (Log Device) corrupted, and I used the command
> sp_attach_single_file_db to attach the DB device to new sql server. But
the
> server responed error:
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database XXXX, CREATE DATABASE is aborted.
> Device activation error. THe physical file name XXXX.LDF may be incorrect.
> I tested use another DB to detach and delete the log file, than attach it
> again. It is work and the new log file will be created.
> What is the problem of my DB device? Is it corrupted as well. Any other
> solution to attach the DB device to SQL server?
> Please help!!!
> Thanks,
> Henry
>|||Hi Henry,
I missed an underscore in the syntax, Use the bleow syntax and contine with
step 8:-
DBCC REBUILD_LOG('dbname','c:\dbname.ldf')
Note:
Replace the dbname and log file name based on ur requirement.
Thanks
Hari
MCDBA
"Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
news:e81qTJaKEHA.2244@.tk2msftngp13.phx.gbl...
> Hi Hari,
> Thanks but in step 7, system prompted error as:
> Server: Msg 2526, Level 16, State 2, Line 1
> Incorrect DBCC statement. Check the documentation for the correct DBCC
> syntax and options.
> I tried in SQL7 ans SQL2000, the result is same. Any setting I've to
change
> to use this command?
> Thanks,
> Henry
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGLeRvVKEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use the procedure
> sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. STart SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILDLOG(dbname,'c:\dbname.ldf')
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
> news:eEjj3xUKEHA.644@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I found my database (Log Device) corrupted, and I used the command
> > sp_attach_single_file_db to attach the DB device to new sql server. But
> the
> > server responed error:
> >
> > Server: Msg 1813, Level 16, State 2, Line 1
> > Could not open new database XXXX, CREATE DATABASE is aborted.
> > Device activation error. THe physical file name XXXX.LDF may be
incorrect.
> >
> > I tested use another DB to detach and delete the log file, than attach
it
> > again. It is work and the new log file will be created.
> > What is the problem of my DB device? Is it corrupted as well. Any other
> > solution to attach the DB device to SQL server?
> >
> > Please help!!!
> >
> > Thanks,
> > Henry
> >
> >
>
>