Hi all i am using crystal report with asp .net.
i am using report files already created an configured with a dsn.
when i try to view the report using crystal report viewer in a web form i am getting the error
"CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed."
i am using
<code>
ConnectionInfo cnnInfo = new ConnectionInfo ( ) ;
cnnInfo.ServerName = strConnInformation[0] ;
cnnInfo.DatabaseName = strConnInformation[1] ;
cnnInfo.UserID = strConnInformation[2] ;
cnnInfo.Password = strConnInformation[3] ;
ReportDocument rptDoc = new ReportDocument ( ) ;
rptDoc.Load ( Server.MapPath ( "..//Reports//" + reportFileName ),
OpenReportMethod.OpenReportByDefault ) ;
//CrystalDecisions.CrystalReports.Engine.Table table = rptDoc.Database.Tables[0] ;
CrystalDecisions.CrystalReports.Engine.Tables rptTables = rptDoc.Database.Tables ;
foreach ( CrystalDecisions.CrystalReports.Engine.Table tbl in rptTables )
{
tblLogOnInfo = tbl.LogOnInfo ;
tblLogOnInfo.ConnectionInfo = cnnInfo ;
tbl.ApplyLogOnInfo ( tblLogOnInfo ) ;
if ( tbl.Location.IndexOf ( "." ) > 0 )
{
tbl.Location = tbl.Location.Substring (
tbl.Location.LastIndexOf(".") + 1 ) ;
}
else
{
//tbl.Location = tbl.Location ;
}
}
</code>
its working fine when i try to execute a report file which configured direct to a database in another machine by specifying the server name, database name, user id and the password,
its showing the error only when i try to execute the report which configured using dsn. dsn is created in my machine and the actual database server is another one.
can any one help me in this matter as i have to use the reports with dsn only in my application.
i got the informations from the net that its same for both type of connectivity to use the ConnectionInfo object and the TableLogonInfo objects.
what my be the problem with me.
is its related to any permision issues.Hai Vinod,
Even i faced the same problem with my C# and Access .
But this prblem arised only when i have a subreport in my main report.
Do u have any subreports in ur appli...If so u have to provide login information to those reports also.
regards,
Kolluru.|||Thanks for your reply kolluru. i will try this and will get back :)|||Do u have any subreports in ur appli...If so u have to provide login information to those reports also.
Im having the same problem and I have subreports. How do you provide the login details for the subreports?sql
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Friday, March 30, 2012
Log issues
Hi All,
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?
2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?
TIA
Joe
1) you can either change the recovery model to simple(if you do not wish to
log the process), however if you are deleting a large number of records in
one transaction(in which case, you will still exceed your configured trans
log size of 400Mb) then you will have to batch these deletes and do them in
smaller/manageable batches.
2) dbcc shrinkfile(<logicalname logfile>,50,turncateonly)
check out BOL for more info.
"JOE" wrote:
> Hi All,
> I have 2 issues with my log files.
> 1) I have a database where I combine 20 tables from 20
> DBs into one very large table. Each table in the 20 DBs
> are the same. My procedure uses 20 insert into
> statements. For some reason my log file fills and the job
> fails. Does anyone know how I can stop the file from
> growing like this?
> 2) I created my Dbs with 400MB trans file. I only use
> about 30MB. Is there a way to drop the size down to 50MB
> and let it grow from there?
> TIA
> Joe
>
|||Thanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.
Joe
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?
2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?
TIA
Joe
1) you can either change the recovery model to simple(if you do not wish to
log the process), however if you are deleting a large number of records in
one transaction(in which case, you will still exceed your configured trans
log size of 400Mb) then you will have to batch these deletes and do them in
smaller/manageable batches.
2) dbcc shrinkfile(<logicalname logfile>,50,turncateonly)
check out BOL for more info.
"JOE" wrote:
> Hi All,
> I have 2 issues with my log files.
> 1) I have a database where I combine 20 tables from 20
> DBs into one very large table. Each table in the 20 DBs
> are the same. My procedure uses 20 insert into
> statements. For some reason my log file fills and the job
> fails. Does anyone know how I can stop the file from
> growing like this?
> 2) I created my Dbs with 400MB trans file. I only use
> about 30MB. Is there a way to drop the size down to 50MB
> and let it grow from there?
> TIA
> Joe
>
|||Thanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.
Joe
Log issues
Hi All,
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?
2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?
TIA
JoeThanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.
Joe
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?
2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?
TIA
JoeThanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.
Joe
Wednesday, March 28, 2012
Log files..
Okay, i must be going crazy. I created a log file;executed the package, it failed in Conrol flow. As expected there was a log file with all the info. Minutes later, i happend to change the connection in the connection manager, and now my log file is not generating any enteries. I have seen this behaviour many times, where it will some times create a log and other time don't. Most of my events are OnError so it should create some log?
I don't understand
-kuspaw
how odd,
when you define a log by means of SSIS log provider for Text Files you must choose for every task all those events in which are you interested (at least on Control Flow layer)
|||Acually it happens quite often when i manually delete contents of log file, it stops writting to it. I tried OnError, OnPreExecute, OnFailure events but no contents in log file. I can see errors in output window but package is not writting anything to log file.
-kushpaw
Log Files!?!?
Hi,
I'm running a SQL Server used only for development and testing. Because of this, a lot of DELETE commands (and other "logable" operations) are issued.
Now, I DON'T WANT to use any logging at all on this server because I don't see the use for it and it's taking too much space on my hard drive.
How can I remove the log files and stop SQL Server from using them? Plus, maybe for some reason that I don't understand, this is not such a good idea. If so, can you please tell me why?
Thanks,
Skip.I'm not aware of a db that is operating normally without a log.
first of all, delete command is logged. If you look for non-logged operations, please check BOL for "bulk insert", "select into", "truncate table" etc. They will not cause the log to grow.
But a lot of times, you have to use "DELETE". you can turn the database into simple recovery mode 'cause it is testing server. The log will be truncated.
You can also run scheduled job to do "backup log xx with truncate_only" with proper frequency.
When none of the above works, you need to look into if you have open transactions by DBCC OPENTRAN. You should also modify your delete statement to do transactions at a smaller scale, say, commit transaction every 100 rows. It will allow the log to be checkpointed and truncated.|||Set the Recovery model of database to "Simple" :)|||Alright, now that's nice!
Still though, my transaction log is 602 Megs, I find it a little too big. I'd like to backup it because, as I understand, it's the only way to reduce it.
Now, when I go to the backup screen, I can't choose to backup my transaction log because the option is disabled. Why is that?
Thanks again,
Skip.|||In the database properties window navigate to Transaction Log tab and see how much is allocated. I suspect that 602 is the number you're going to see. You'll probably need to do shrinkfile against your trx log. The reason Backup Transaction Log option is disabled is because your database is in Simple Recovery mode.
I'm running a SQL Server used only for development and testing. Because of this, a lot of DELETE commands (and other "logable" operations) are issued.
Now, I DON'T WANT to use any logging at all on this server because I don't see the use for it and it's taking too much space on my hard drive.
How can I remove the log files and stop SQL Server from using them? Plus, maybe for some reason that I don't understand, this is not such a good idea. If so, can you please tell me why?
Thanks,
Skip.I'm not aware of a db that is operating normally without a log.
first of all, delete command is logged. If you look for non-logged operations, please check BOL for "bulk insert", "select into", "truncate table" etc. They will not cause the log to grow.
But a lot of times, you have to use "DELETE". you can turn the database into simple recovery mode 'cause it is testing server. The log will be truncated.
You can also run scheduled job to do "backup log xx with truncate_only" with proper frequency.
When none of the above works, you need to look into if you have open transactions by DBCC OPENTRAN. You should also modify your delete statement to do transactions at a smaller scale, say, commit transaction every 100 rows. It will allow the log to be checkpointed and truncated.|||Set the Recovery model of database to "Simple" :)|||Alright, now that's nice!
Still though, my transaction log is 602 Megs, I find it a little too big. I'd like to backup it because, as I understand, it's the only way to reduce it.
Now, when I go to the backup screen, I can't choose to backup my transaction log because the option is disabled. Why is that?
Thanks again,
Skip.|||In the database properties window navigate to Transaction Log tab and see how much is allocated. I suspect that 602 is the number you're going to see. You'll probably need to do shrinkfile against your trx log. The reason Backup Transaction Log option is disabled is because your database is in Simple Recovery mode.
Log files WAY too big
I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default...b;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIA
Open Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
sql
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default...b;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIA
Open Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
sql
Log files WAY too big
I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
Log files WAY too big
I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/defaul...kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/defaul...kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
Log files taking up a lot of disk space
Hello,
I ran into an issue with all these logs made by default.
Disk space crunch leading to performance degradation.
OK, I moved the log dir outside the default app volume (Script/restart) onto a 100GB drive I use for the SQL transactions logs, but.
Analysis Services accumulated 20GB of logs in 2-3 days, did not clean-up the old stuff and led to disk space alerts and applications performance issues. All this with out of the box settings.
The server has in the 30 to 60 connections alive around the clock with each of these connections performing queries every 2-3 seconds or so.
I also use the query log function for optimisation purposes but for that one I log to table on a specific drive.
I run Analysis services 64 bits build 9.00.2047
Log files eating the drive have name like msmdsrv.log, FlightRecorderCurrent.trc, FlightRecorderBack.trc, SQLDmpr4495.mdmp, SQLDmpr4495.log and SQLDUMPER_ERRORLOG.log
Is the high frequency of log files creation (4MB every 5 minutes) the indication of a bigger issue?
Is there any way to set up these logs to be less intrusive, less verbose and more self-cleaning while still reaping the benefits of unattended problems loging?
Thanks,
Philippe
Yes.
You might be having different and potentially more seriouse issues. Creation of SQLDmpr***.mdmp SQLDmpr***.log files is indication of something going wrong in your server.
You should try and contact customer support and report your problems.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thank you,
The problem was very simple, the login in the QueryLog connection string did not have write access to the logDB.
This was generating all these Dmpr log files. Not a single log was generated since I fixed it last week.
Philippe
|||I split and edited this thread from an unrelated thread on the Flight Recorder to make it easier for people searching the forum to find this information.log files problem
Hi All,
I have problem with log files on my databases, they grow so much that I have
errors when I backup and restore the data.
The best way that I found to fix this is to detach the db, delete the log
file and attach the data again.
How can I do this programmatically?
Tks in advance
JohnnyThe best way to fix this is to manage the log properly so that it
doesn't grow. If you aren't performing Log backups then set Simple
Recovery mode. On an OLTP system you should not need to allow log files
to grow (disable autogrow) and you should avoid shrinking at all.
David Portas
SQL Server MVP
--|||You can truncate the log file to 1KB using the DBCC command , so do it in a
periodical basis
"JFB" wrote:
> Hi All,
> I have problem with log files on my databases, they grow so much that I ha
ve
> errors when I backup and restore the data.
> The best way that I found to fix this is to detach the db, delete the log
> file and attach the data again.
> How can I do this programmatically?
> Tks in advance
> Johnny
>
>|||Look at "recovering databases-SQL Server" in BOL.
If you don't need transaction logs, put the DB in simple recovery mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
> a
> periodical basis
> "JFB" wrote:
>|||Why? Periodically shrinking has di
vantages: http://www.karaszi.com/SQLServer/in...dont_shrink.asp
JFB's problem is probably that db is in full recovery mode and periodic log
backups are not
performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
a
> periodical basis
> "JFB" wrote:
>
I have problem with log files on my databases, they grow so much that I have
errors when I backup and restore the data.
The best way that I found to fix this is to detach the db, delete the log
file and attach the data again.
How can I do this programmatically?
Tks in advance
JohnnyThe best way to fix this is to manage the log properly so that it
doesn't grow. If you aren't performing Log backups then set Simple
Recovery mode. On an OLTP system you should not need to allow log files
to grow (disable autogrow) and you should avoid shrinking at all.
David Portas
SQL Server MVP
--|||You can truncate the log file to 1KB using the DBCC command , so do it in a
periodical basis
"JFB" wrote:
> Hi All,
> I have problem with log files on my databases, they grow so much that I ha
ve
> errors when I backup and restore the data.
> The best way that I found to fix this is to detach the db, delete the log
> file and attach the data again.
> How can I do this programmatically?
> Tks in advance
> Johnny
>
>|||Look at "recovering databases-SQL Server" in BOL.
If you don't need transaction logs, put the DB in simple recovery mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
> a
> periodical basis
> "JFB" wrote:
>|||Why? Periodically shrinking has di
vantages: http://www.karaszi.com/SQLServer/in...dont_shrink.aspJFB's problem is probably that db is in full recovery mode and periodic log
backups are not
performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
a
> periodical basis
> "JFB" wrote:
>
Log files must be named *_log.ldf?
Hello,
I've been having problems with SQL Express when my database files are named basename.mdf and basename.ldf (e.g. on using the Access upsizing wizard).
At points that I haven't fully identified yet (I'm using VB Express clients), a second log file named basename_log.mdf gets created and SQL Express gets confused and compalins the files are corrupted.
Can anyone explain what is happening?
TIA,
Bill
SQL Server files do not really need to have a specific extension, so it might be possible that this file, although having a ldf extension is not a ldf file. Which files did the wizard create for you ?HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Hello Jens.
My problem is not with the ldf extension but with a second transaction log file getting created.
As an example:
1) Create a new Access database namedmyDatabase.mdb (containing a single table.)
2) Use the Upsizing wizard to create a new SQL database from the Access database accepting the defaults as much as possible. This creates files named myDatabaseSQL.mdf and myDatabaseSQL.ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
3) The database myDatabaseSql is already visible when you next open up MSS MSE, from where you can see the data in the one table.
4) So far everything is ok.
5) Now exit MSS MSE and start VB 2k5 Express. Without opening up any project or solution, simply open the Database Explorer and create a new connection to myDatabaseSQL. Hit the Test Connection button and once told it’s ok, immediately exit VB.
6) At this point there are now three files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data :
myDatabaseSQL.mdf
myDatabaseSQL.ldf
myDataBaseSQL_log.ldf (this one seems to be created by VB's Database Explorer)
7) Now open up MSS MSE again and try to get to table within myDatabaseSQL from the object explorer. On clicking on myDatabaseSQL you get the following error message
One or more files do not match the primary file of the database….. If this is an existing database the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error 5173)
This behaviour is repeated on all my databases including Adventure Works and NorthWind.
I got the problem on the ‘smaller ‘ SQL Server Express + MSS MSE down loads on one machine and also get the problem with the ‘larger’ SSE with Advanced Services + Toolkit downloads on a second machine.
Both machines are running XP Pro + Office 2003 + 2005 Express versions of SS + VB.
Hope this lot help.
TIA
Bill
sql
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Log Files - possible to read them ?
We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>> We have a user who we think is connecting to our DB through Access and
>> accidently changes records. (He needs to be able to make changes
>> elsewhere so he does need write access).
>> We have tossed around the idea of giving him a read only account that he
>> would connect with but that was rejected - he will use his account with
>> write privileges.
>> So my question is - can we use the transaction logs to see what the user
>> is doing? What format is the transaction log in? Anything useful.
>> TIA - Jeff.
>>
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>> We have a user who we think is connecting to our DB through Access and
>> accidently changes records. (He needs to be able to make changes
>> elsewhere so he does need write access).
>> We have tossed around the idea of giving him a read only account that he
>> would connect with but that was rejected - he will use his account with
>> write privileges.
>> So my question is - can we use the transaction logs to see what the user
>> is doing? What format is the transaction log in? Anything useful.
>> TIA - Jeff.
>>
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
Log Files - possible to read them ?
We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspf...
log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspf...-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspf...
log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspf...-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
Log Files - possible to read them ?
We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.
There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.
There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>
Log files - Location
Does the log file have to be on the same drive as the data file?
I know how to detach the database, move location of database files, and attach database pointing to new location. But, I can't get it to work if the log file is on a drive different than the data file.
Is there another way to do this?
Thanks!
No, the log files do not have to be on the same drive as the data files. =
In fact, it is generally recommended that these be located on different =
drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =
DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =
file(s) to different disk locations. Documentation and examples can be =
found within Books Online (within the SQL Server program group).
--=20
Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
>=20
> I know how to detach the database, move location of database files, =
and attach database pointing to new location. But, I can't get it to =
work if the log file is on a drive different than the data file.
>=20
> Is there another way to do this?
>=20
> Thanks!
|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname>,'physical_mdf_file_with_path','Physical_L DF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!
sql
I know how to detach the database, move location of database files, and attach database pointing to new location. But, I can't get it to work if the log file is on a drive different than the data file.
Is there another way to do this?
Thanks!
No, the log files do not have to be on the same drive as the data files. =
In fact, it is generally recommended that these be located on different =
drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =
DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =
file(s) to different disk locations. Documentation and examples can be =
found within Books Online (within the SQL Server program group).
--=20
Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
>=20
> I know how to detach the database, move location of database files, =
and attach database pointing to new location. But, I can't get it to =
work if the log file is on a drive different than the data file.
>=20
> Is there another way to do this?
>=20
> Thanks!
|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname>,'physical_mdf_file_with_path','Physical_L DF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!
sql
Log files - Location
Does the log file have to be on the same drive as the data file?
I know how to detach the database, move location of database files, and atta
ch database pointing to new location. But, I can't get it to work if the log
file is on a drive different than the data file.
Is there another way to do this?
Thanks!No, the log files do not have to be on the same drive as the data files. =
In fact, it is generally recommended that these be located on different =
drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =
DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =
file(s) to different disk locations. Documentation and examples can be =
found within Books Online (within the SQL Server program group).
--=20
Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
>=20
> I know how to detach the database, move location of database files, =
and attach database pointing to new location. But, I can't get it to =
work if the log file is on a drive different than the data file.
>=20
> Is there another way to do this?
>=20
> Thanks!|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname> ,'physical_mdf_file_with_path','Physical
_LDF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!
I know how to detach the database, move location of database files, and atta
ch database pointing to new location. But, I can't get it to work if the log
file is on a drive different than the data file.
Is there another way to do this?
Thanks!No, the log files do not have to be on the same drive as the data files. =
In fact, it is generally recommended that these be located on different =
drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =
DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =
file(s) to different disk locations. Documentation and examples can be =
found within Books Online (within the SQL Server program group).
--=20
Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
>=20
> I know how to detach the database, move location of database files, =
and attach database pointing to new location. But, I can't get it to =
work if the log file is on a drive different than the data file.
>=20
> Is there another way to do this?
>=20
> Thanks!|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname> ,'physical_mdf_file_with_path','Physical
_LDF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!
Log files - Location
Does the log file have to be on the same drive as the data file
I know how to detach the database, move location of database files, and attach database pointing to new location. But, I can't get it to work if the log file is on a drive different than the data file
Is there another way to do this
Thanks!No, the log files do not have to be on the same drive as the data files. = In fact, it is generally recommended that these be located on different =drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =file(s) to different disk locations. Documentation and examples can be =found within Books Online (within the SQL Server program group).
-- Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> > I know how to detach the database, move location of database files, =and attach database pointing to new location. But, I can't get it to =work if the log file is on a drive different than the data file.
> > Is there another way to do this?
> > Thanks!|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname>,'physical_mdf_file_with_path','Physical_LDF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!
I know how to detach the database, move location of database files, and attach database pointing to new location. But, I can't get it to work if the log file is on a drive different than the data file
Is there another way to do this
Thanks!No, the log files do not have to be on the same drive as the data files. = In fact, it is generally recommended that these be located on different =drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =file(s) to different disk locations. Documentation and examples can be =found within Books Online (within the SQL Server program group).
-- Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> > I know how to detach the database, move location of database files, =and attach database pointing to new location. But, I can't get it to =work if the log file is on a drive different than the data file.
> > Is there another way to do this?
> > Thanks!|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname>,'physical_mdf_file_with_path','Physical_LDF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!
Subscribe to:
Posts (Atom)