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
>
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Log files
Is there any way to turn off recovery and/or eliminate the growth of the log
file? I've heard there's a system stored procedure which will do this, but
I am unable to find any info either in the help or googling the net.
Thanks,
JimOn Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
There isn't such stored procedure but it sounds like you are not
using log backups, but your database is not configured as simple
recovery mode. If this is correct, you can modify the database so it
will be in simple recovery mode using this statement:
alter database WriteRealDBNameHere set recovery simple
This will prevent the log from storing the data modifications
operations after they are done, but you still have to shrink the file
if it is too big. Notice that when you set the database to simple
recovery then you can restore it only to the last full or differential
backups that you have and in case of a catastrophic error, you will
not be able to do restore to a point of time.
Adi|||We are using simple recovery. Backup and recoveries are not needed for our
purposes - basically we do complex queries against fixed data sets (no
transactions) but do create tables of intermediate results, and the log
files (even in Simple recovery) get quite large. Surprised to learn that
logging cannot be turned off.
Thanks.
"Adi" <adicohn@.hotmail.com> wrote in message
news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
> There isn't such stored procedure but it sounds like you are not
> using log backups, but your database is not configured as simple
> recovery mode. If this is correct, you can modify the database so it
> will be in simple recovery mode using this statement:
> alter database WriteRealDBNameHere set recovery simple
> This will prevent the log from storing the data modifications
> operations after they are done, but you still have to shrink the file
> if it is too big. Notice that when you set the database to simple
> recovery then you can restore it only to the last full or differential
> backups that you have and in case of a catastrophic error, you will
> not be able to do restore to a point of time.
> Adi
>|||Hello Jim!
I believe that the following documentation will enlighten you about the
growing of Transaction Log.
http://support.microsoft.com/kb/873235/en-us
Ekrem Önsoy
"Jim Fox" <jim.fox@.emailhdi.com> wrote in message
news:e%23sjIRh%23HHA.464@.TK2MSFTNGP02.phx.gbl...
> We are using simple recovery. Backup and recoveries are not needed for
> our purposes - basically we do complex queries against fixed data sets (no
> transactions) but do create tables of intermediate results, and the log
> files (even in Simple recovery) get quite large. Surprised to learn that
> logging cannot be turned off.
> Thanks.
>
> "Adi" <adicohn@.hotmail.com> wrote in message
> news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
>> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
>> There isn't such stored procedure but it sounds like you are not
>> using log backups, but your database is not configured as simple
>> recovery mode. If this is correct, you can modify the database so it
>> will be in simple recovery mode using this statement:
>> alter database WriteRealDBNameHere set recovery simple
>> This will prevent the log from storing the data modifications
>> operations after they are done, but you still have to shrink the file
>> if it is too big. Notice that when you set the database to simple
>> recovery then you can restore it only to the last full or differential
>> backups that you have and in case of a catastrophic error, you will
>> not be able to do restore to a point of time.
>> Adi
>|||In article <e#sjIRh#HHA.464@.TK2MSFTNGP02.phx.gbl>, jim.fox@.emailhdi.com
says...
> Surprised to learn that
> logging cannot be turned off.
>
I think you are confused by the use of the word "logging." Not like IIS
logging, or event logs, or installation logs which are all
informational. The LDF file is an inherent part of the database, such
that at any time, "database" consists of stuff already rolled into the
MDF/NDF files and stuff that is still in the "LDF" file. One cannot
exist without the other and you sure do *not* want to have the ability
to turn off this write-ahead logging. If you could you would break the
database and render it completely unusable. It would cause the A-C-I-D
properties to be broken. Without Atomic-Consistent-Isolated-Durable
transactions you end up with things like phantom records, lost data,
etc.
Suggest you read about transactions and transaction isolation in BOL
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||You can do this in the foolowing steps:
1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
2. restart the server
Now your database in so called "emergency mode". One of properties of this
mode is that database operations are not logged.
Of course, it is that you want, but on my opinion it is not clever to do
such a things.
"Jim Fox" wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
>
>|||On Sep 19, 1:28 pm, Dmitrij Siemieniako
<DmitrijSiemieni...@.discussions.microsoft.com> wrote:
> You can do this in the foolowing steps:
> 1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
> 2. restart the server
> Now your database in so called "emergency mode". One of properties of this
> mode is that database operations are not logged.
> Of course, it is that you want, but on my opinion it is not clever to do
> such a things.
>
> "Jim Fox" wrote:
> > Is there any way to turn off recovery and/or eliminate the growth of the log
> > file? I've heard there's a system stored procedure which will do this, but
> > I am unable to find any info either in the help or googling the net.
> > Thanks,
> > Jim- Hide quoted text -
> - Show quoted text -
Emergency mode should be used (just as the name implies) on emergency
cases only. It should be used on cases such as there is no log file
or cases with this severity. It definitely shouldn't be used on
regular basis for no reason. On this case it also wouldn't help.
When you set a database to emergency mode, you can't start any
transaction and you can not perform any data modification operation
within the database. Since Jim wrote that he inserts data into tables
and then base the reports on those tables, his procedure will not work
on a database that is set to emergency mode.
One last thing - If I remember correctly, setting a database to
emergency mode requires you to set the status to -32768 (I think that
the documentation specifies 32768, and that it is wrong. I admit that
I'm not sure about it).
Adi
file? I've heard there's a system stored procedure which will do this, but
I am unable to find any info either in the help or googling the net.
Thanks,
JimOn Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
There isn't such stored procedure but it sounds like you are not
using log backups, but your database is not configured as simple
recovery mode. If this is correct, you can modify the database so it
will be in simple recovery mode using this statement:
alter database WriteRealDBNameHere set recovery simple
This will prevent the log from storing the data modifications
operations after they are done, but you still have to shrink the file
if it is too big. Notice that when you set the database to simple
recovery then you can restore it only to the last full or differential
backups that you have and in case of a catastrophic error, you will
not be able to do restore to a point of time.
Adi|||We are using simple recovery. Backup and recoveries are not needed for our
purposes - basically we do complex queries against fixed data sets (no
transactions) but do create tables of intermediate results, and the log
files (even in Simple recovery) get quite large. Surprised to learn that
logging cannot be turned off.
Thanks.
"Adi" <adicohn@.hotmail.com> wrote in message
news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
> There isn't such stored procedure but it sounds like you are not
> using log backups, but your database is not configured as simple
> recovery mode. If this is correct, you can modify the database so it
> will be in simple recovery mode using this statement:
> alter database WriteRealDBNameHere set recovery simple
> This will prevent the log from storing the data modifications
> operations after they are done, but you still have to shrink the file
> if it is too big. Notice that when you set the database to simple
> recovery then you can restore it only to the last full or differential
> backups that you have and in case of a catastrophic error, you will
> not be able to do restore to a point of time.
> Adi
>|||Hello Jim!
I believe that the following documentation will enlighten you about the
growing of Transaction Log.
http://support.microsoft.com/kb/873235/en-us
Ekrem Önsoy
"Jim Fox" <jim.fox@.emailhdi.com> wrote in message
news:e%23sjIRh%23HHA.464@.TK2MSFTNGP02.phx.gbl...
> We are using simple recovery. Backup and recoveries are not needed for
> our purposes - basically we do complex queries against fixed data sets (no
> transactions) but do create tables of intermediate results, and the log
> files (even in Simple recovery) get quite large. Surprised to learn that
> logging cannot be turned off.
> Thanks.
>
> "Adi" <adicohn@.hotmail.com> wrote in message
> news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
>> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
>> There isn't such stored procedure but it sounds like you are not
>> using log backups, but your database is not configured as simple
>> recovery mode. If this is correct, you can modify the database so it
>> will be in simple recovery mode using this statement:
>> alter database WriteRealDBNameHere set recovery simple
>> This will prevent the log from storing the data modifications
>> operations after they are done, but you still have to shrink the file
>> if it is too big. Notice that when you set the database to simple
>> recovery then you can restore it only to the last full or differential
>> backups that you have and in case of a catastrophic error, you will
>> not be able to do restore to a point of time.
>> Adi
>|||In article <e#sjIRh#HHA.464@.TK2MSFTNGP02.phx.gbl>, jim.fox@.emailhdi.com
says...
> Surprised to learn that
> logging cannot be turned off.
>
I think you are confused by the use of the word "logging." Not like IIS
logging, or event logs, or installation logs which are all
informational. The LDF file is an inherent part of the database, such
that at any time, "database" consists of stuff already rolled into the
MDF/NDF files and stuff that is still in the "LDF" file. One cannot
exist without the other and you sure do *not* want to have the ability
to turn off this write-ahead logging. If you could you would break the
database and render it completely unusable. It would cause the A-C-I-D
properties to be broken. Without Atomic-Consistent-Isolated-Durable
transactions you end up with things like phantom records, lost data,
etc.
Suggest you read about transactions and transaction isolation in BOL
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||You can do this in the foolowing steps:
1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
2. restart the server
Now your database in so called "emergency mode". One of properties of this
mode is that database operations are not logged.
Of course, it is that you want, but on my opinion it is not clever to do
such a things.
"Jim Fox" wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
>
>|||On Sep 19, 1:28 pm, Dmitrij Siemieniako
<DmitrijSiemieni...@.discussions.microsoft.com> wrote:
> You can do this in the foolowing steps:
> 1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
> 2. restart the server
> Now your database in so called "emergency mode". One of properties of this
> mode is that database operations are not logged.
> Of course, it is that you want, but on my opinion it is not clever to do
> such a things.
>
> "Jim Fox" wrote:
> > Is there any way to turn off recovery and/or eliminate the growth of the log
> > file? I've heard there's a system stored procedure which will do this, but
> > I am unable to find any info either in the help or googling the net.
> > Thanks,
> > Jim- Hide quoted text -
> - Show quoted text -
Emergency mode should be used (just as the name implies) on emergency
cases only. It should be used on cases such as there is no log file
or cases with this severity. It definitely shouldn't be used on
regular basis for no reason. On this case it also wouldn't help.
When you set a database to emergency mode, you can't start any
transaction and you can not perform any data modification operation
within the database. Since Jim wrote that he inserts data into tables
and then base the reports on those tables, his procedure will not work
on a database that is set to emergency mode.
One last thing - If I remember correctly, setting a database to
emergency mode requires you to set the status to -32768 (I think that
the documentation specifies 32768, and that it is wrong. I admit that
I'm not sure about it).
Adi
Log files
I am getting the error message ,
"Could not allocate space for object '(SYSTEM table id: -1021390423)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full."
when i run a select stmt. I am thinking it's because my log file is full.
Can you pls tell me what i am supposed to do now?Read the error message closely, as it explains the problem very well. The pr
oblem is the tempdb
database. It is not the transaction log for tempdb, it is the database files
. They are not large
enough, and sometimes autogrow doesn't grow fast enough for the space needed
by the query
processing. Either pre.allocate storage for tempdb, or tweak the query (look
at query plan, add
indexes, modify the SQL etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>I am getting the error message ,
> "Could not allocate space for object '(SYSTEM table id: -1021390423)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full."
> when i run a select stmt. I am thinking it's because my log file is full.
> Can you pls tell me what i am supposed to do now?|||How do I make them grow?
"Tibor Karaszi" wrote:
> Read the error message closely, as it explains the problem very well. The
problem is the tempdb
> database. It is not the transaction log for tempdb, it is the database fil
es. They are not large
> enough, and sometimes autogrow doesn't grow fast enough for the space need
ed by the query
> processing. Either pre.allocate storage for tempdb, or tweak the query (lo
ok at query plan, add
> indexes, modify the SQL etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>
>|||Should i go to properties,data files and increase the size for space
allocated? Pls reply. Thanks in advance.
"PH" wrote:
> How do I make them grow?
> "Tibor Karaszi" wrote:
>|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:BA24839D-8D6E-4263-999A-2CF015CFAAA0@.microsoft.com...
> Should i go to properties,data files and increase the size for space
> allocated? Pls reply. Thanks in advance.
> "PH" wrote:
>
"Could not allocate space for object '(SYSTEM table id: -1021390423)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full."
when i run a select stmt. I am thinking it's because my log file is full.
Can you pls tell me what i am supposed to do now?Read the error message closely, as it explains the problem very well. The pr
oblem is the tempdb
database. It is not the transaction log for tempdb, it is the database files
. They are not large
enough, and sometimes autogrow doesn't grow fast enough for the space needed
by the query
processing. Either pre.allocate storage for tempdb, or tweak the query (look
at query plan, add
indexes, modify the SQL etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>I am getting the error message ,
> "Could not allocate space for object '(SYSTEM table id: -1021390423)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full."
> when i run a select stmt. I am thinking it's because my log file is full.
> Can you pls tell me what i am supposed to do now?|||How do I make them grow?
"Tibor Karaszi" wrote:
> Read the error message closely, as it explains the problem very well. The
problem is the tempdb
> database. It is not the transaction log for tempdb, it is the database fil
es. They are not large
> enough, and sometimes autogrow doesn't grow fast enough for the space need
ed by the query
> processing. Either pre.allocate storage for tempdb, or tweak the query (lo
ok at query plan, add
> indexes, modify the SQL etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>
>|||Should i go to properties,data files and increase the size for space
allocated? Pls reply. Thanks in advance.
"PH" wrote:
> How do I make them grow?
> "Tibor Karaszi" wrote:
>|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:BA24839D-8D6E-4263-999A-2CF015CFAAA0@.microsoft.com...
> Should i go to properties,data files and increase the size for space
> allocated? Pls reply. Thanks in advance.
> "PH" wrote:
>
Friday, March 23, 2012
Log File Size Maintenance
Hi,
I am using SQL2000 Standard.
The log file running up tremendously after the system life in production.
My ratio of size is 200mb for Data files and 650mb for Log file, in a month.
Apparently I wish to know:
1) What is cause of the Log file turn big? I am worry it was cause by error
(any error eg, ADO or SQL Transaction Log).
2) It is possible to read / study the log file content?
3) How to Backup or Truncate the log file?
4) What is the Standard or Typical settting in SQL Server Properties and
Configuration in regards to Log File maintenance? I am using all default
setting now.
Thanks.> 1) What is cause of the Log file turn big? I am worry it was cause by
error
> (any error eg, ADO or SQL Transaction Log).
Full or Bulk Logged recovery model without a backup plan, I guess in your
case.
> 2) It is possible to read / study the log file content?
Log Explorer - www.lumigent.com.
> 3) How to Backup or Truncate the log file?
With Backup Log T-SQL statement. Check the syntax in Books OnLine.
> 4) What is the Standard or Typical settting in SQL Server Properties and
> Configuration in regards to Log File maintenance? I am using all default
> setting now.
Default settings differ for server and desktop editions for SQL Server, so
you didn't give us much info. Anyway, for a server in production, you should
use Full recovery model with appropriate backup plan.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
I am using SQL2000 Standard.
The log file running up tremendously after the system life in production.
My ratio of size is 200mb for Data files and 650mb for Log file, in a month.
Apparently I wish to know:
1) What is cause of the Log file turn big? I am worry it was cause by error
(any error eg, ADO or SQL Transaction Log).
2) It is possible to read / study the log file content?
3) How to Backup or Truncate the log file?
4) What is the Standard or Typical settting in SQL Server Properties and
Configuration in regards to Log File maintenance? I am using all default
setting now.
Thanks.> 1) What is cause of the Log file turn big? I am worry it was cause by
error
> (any error eg, ADO or SQL Transaction Log).
Full or Bulk Logged recovery model without a backup plan, I guess in your
case.
> 2) It is possible to read / study the log file content?
Log Explorer - www.lumigent.com.
> 3) How to Backup or Truncate the log file?
With Backup Log T-SQL statement. Check the syntax in Books OnLine.
> 4) What is the Standard or Typical settting in SQL Server Properties and
> Configuration in regards to Log File maintenance? I am using all default
> setting now.
Default settings differ for server and desktop editions for SQL Server, so
you didn't give us much info. Anyway, for a server in production, you should
use Full recovery model with appropriate backup plan.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Wednesday, March 21, 2012
Log file is not available
I have a database of around 100 GB. It was de-attached
from the system. When I attach it to the system, it says
log file is not available though it is getting attached.
When I do any transaction, it says Log file is not
available. Kindly HelpHi,
Try to use below DBCC command. Since it a undocumented command please
contact your primary support before usage.
dbcc rebuild_log('dbname','C:\dbname_log.ldf')
Thanks
Hari
MCDBA
"Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
> I have a database of around 100 GB. It was de-attached
> from the system. When I attach it to the system, it says
> log file is not available though it is getting attached.
> When I do any transaction, it says Log file is not
> available. Kindly Help|||Hari
Yes it is in case of emergency.
Fully supported way to do it is backup log 'dbname' to... with no_truncate
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to use below DBCC command. Since it a undocumented command please
> contact your primary support before usage.
> dbcc rebuild_log('dbname','C:\dbname_log.ldf')
> Thanks
> Hari
> MCDBA
>
> "Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
> news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
>|||Hi
What do you mean my fully supported?. howcan I backup the
logfile when I do not have log file associated with my mdf
file.I mean if I do any transaction, it would first
display a message that log file is not avaialable.Pls
explain as its going out of control.
Regards
Pankaj
>--Original Message--
>Hari
>Yes it is in case of emergency.
>Fully supported way to do it is backup log 'dbname'
to... with no_truncate
>
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message
>news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
command please
wrote in message
says
attached.
>
>.
>sql
from the system. When I attach it to the system, it says
log file is not available though it is getting attached.
When I do any transaction, it says Log file is not
available. Kindly HelpHi,
Try to use below DBCC command. Since it a undocumented command please
contact your primary support before usage.
dbcc rebuild_log('dbname','C:\dbname_log.ldf')
Thanks
Hari
MCDBA
"Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
> I have a database of around 100 GB. It was de-attached
> from the system. When I attach it to the system, it says
> log file is not available though it is getting attached.
> When I do any transaction, it says Log file is not
> available. Kindly Help|||Hari
Yes it is in case of emergency.
Fully supported way to do it is backup log 'dbname' to... with no_truncate
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to use below DBCC command. Since it a undocumented command please
> contact your primary support before usage.
> dbcc rebuild_log('dbname','C:\dbname_log.ldf')
> Thanks
> Hari
> MCDBA
>
> "Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
> news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
>|||Hi
What do you mean my fully supported?. howcan I backup the
logfile when I do not have log file associated with my mdf
file.I mean if I do any transaction, it would first
display a message that log file is not avaialable.Pls
explain as its going out of control.
Regards
Pankaj
>--Original Message--
>Hari
>Yes it is in case of emergency.
>Fully supported way to do it is backup log 'dbname'
to... with no_truncate
>
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message
>news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
command please
wrote in message
says
attached.
>
>.
>sql
Log file is not available
I have a database of around 100 GB. It was de-attached
from the system. When I attach it to the system, it says
log file is not available though it is getting attached.
When I do any transaction, it says Log file is not
available. Kindly HelpHi,
Try to use below DBCC command. Since it a undocumented command please
contact your primary support before usage.
dbcc rebuild_log('dbname','C:\dbname_log.ldf')
Thanks
Hari
MCDBA
"Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
> I have a database of around 100 GB. It was de-attached
> from the system. When I attach it to the system, it says
> log file is not available though it is getting attached.
> When I do any transaction, it says Log file is not
> available. Kindly Help|||Hari
Yes it is in case of emergency.
Fully supported way to do it is backup log 'dbname' to... with no_truncate
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to use below DBCC command. Since it a undocumented command please
> contact your primary support before usage.
> dbcc rebuild_log('dbname','C:\dbname_log.ldf')
> Thanks
> Hari
> MCDBA
>
> "Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
> news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
> > I have a database of around 100 GB. It was de-attached
> > from the system. When I attach it to the system, it says
> > log file is not available though it is getting attached.
> > When I do any transaction, it says Log file is not
> > available. Kindly Help
>|||Hi
What do you mean my fully supported?. howcan I backup the
logfile when I do not have log file associated with my mdf
file.I mean if I do any transaction, it would first
display a message that log file is not avaialable.Pls
explain as its going out of control.
Regards
Pankaj
>--Original Message--
>Hari
>Yes it is in case of emergency.
>Fully supported way to do it is backup log 'dbname'
to... with no_truncate
>
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message
>news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Try to use below DBCC command. Since it a undocumented
command please
>> contact your primary support before usage.
>> dbcc rebuild_log('dbname','C:\dbname_log.ldf')
>> Thanks
>> Hari
>> MCDBA
>>
>> "Pankaj Agarwal" <anonymous@.discussions.microsoft.com>
wrote in message
>> news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
>> > I have a database of around 100 GB. It was de-attached
>> > from the system. When I attach it to the system, it
says
>> > log file is not available though it is getting
attached.
>> > When I do any transaction, it says Log file is not
>> > available. Kindly Help
>>
>
>.
>
from the system. When I attach it to the system, it says
log file is not available though it is getting attached.
When I do any transaction, it says Log file is not
available. Kindly HelpHi,
Try to use below DBCC command. Since it a undocumented command please
contact your primary support before usage.
dbcc rebuild_log('dbname','C:\dbname_log.ldf')
Thanks
Hari
MCDBA
"Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
> I have a database of around 100 GB. It was de-attached
> from the system. When I attach it to the system, it says
> log file is not available though it is getting attached.
> When I do any transaction, it says Log file is not
> available. Kindly Help|||Hari
Yes it is in case of emergency.
Fully supported way to do it is backup log 'dbname' to... with no_truncate
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to use below DBCC command. Since it a undocumented command please
> contact your primary support before usage.
> dbcc rebuild_log('dbname','C:\dbname_log.ldf')
> Thanks
> Hari
> MCDBA
>
> "Pankaj Agarwal" <anonymous@.discussions.microsoft.com> wrote in message
> news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
> > I have a database of around 100 GB. It was de-attached
> > from the system. When I attach it to the system, it says
> > log file is not available though it is getting attached.
> > When I do any transaction, it says Log file is not
> > available. Kindly Help
>|||Hi
What do you mean my fully supported?. howcan I backup the
logfile when I do not have log file associated with my mdf
file.I mean if I do any transaction, it would first
display a message that log file is not avaialable.Pls
explain as its going out of control.
Regards
Pankaj
>--Original Message--
>Hari
>Yes it is in case of emergency.
>Fully supported way to do it is backup log 'dbname'
to... with no_truncate
>
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message
>news:OWTx6nBAEHA.2040@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Try to use below DBCC command. Since it a undocumented
command please
>> contact your primary support before usage.
>> dbcc rebuild_log('dbname','C:\dbname_log.ldf')
>> Thanks
>> Hari
>> MCDBA
>>
>> "Pankaj Agarwal" <anonymous@.discussions.microsoft.com>
wrote in message
>> news:4c3c01c3ffc3$399864a0$a001280a@.phx.gbl...
>> > I have a database of around 100 GB. It was de-attached
>> > from the system. When I attach it to the system, it
says
>> > log file is not available though it is getting
attached.
>> > When I do any transaction, it says Log file is not
>> > available. Kindly Help
>>
>
>.
>
Monday, March 12, 2012
log file dilemma! share disk with OS or Data file?
Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partiti
on
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on tha
t
> volume. The advantage is that Bigger disk space usage but very bad on syst
em
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAI
D
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose betwe
en
> no. 1 and 2, which would you prefer? and how bad the performance hit will
be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partiti
on
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on tha
t
> volume. The advantage is that Bigger disk space usage but very bad on syst
em
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAI
D
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose betwe
en
> no. 1 and 2, which would you prefer? and how bad the performance hit will
be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>
log file dilemma! share disk with OS or Data file?
Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2
I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2
I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>
log file dilemma! share disk with OS or Data file?
Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>
Subscribe to:
Posts (Atom)