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 file sizes for all databases

Hi All,

I ran the following query to get the log file sizes for all databases:

select (size*8.00)/1024,filename from master..sysaltfiles

When I compared the results from this query with the transaction log properties in EM, not all of the sizes match.
For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.I have checked that and find the difference.

Run the below code which gives you the size of tempdb which is as same as physically stored file on your harddisk.

sp_helpdb 'tempdb'

or

select name, filename, (size*8) as size from tempdb..sysfiles

The size in the master..sysaltfiles will helps you to determine the size of files in the tempdb when SQL Server was last started.

Refer the below web page & Sr # 6. RESOURCES

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=39550|||I've ran the statement that you sent me and I am still coming up with wrong number.|||I've ran the statement that you sent me and I am still coming up with wrong number.

The statement I have given will help you to figure out current tempdb log size.

The size in the master..sysaltfiles will help you to determine the size of the files in the tempdb when SQL Server was last started.

Refer the link I have posted above.

Log File Issue

I am still struguling with shrinking a log file that is
almost 5GB. The database is being replicated using merge
replication. I ran the following commands without any
success
backup log Main_DB with truncate_only
The log was not truncated because records at the
beginning of the log are pending replication. Ensure the
Log Reader Agent is running or use sp_repldone to mark
transactions as distributed.
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Server: Msg 18757, Level 16, State 1, Procedure
sp_repldone, Line 1
The database is not published.
dbcc opentran
Transaction information for database 'Main_DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
How do I go about shrinking/truncating the log file
without having to remove replication from the database or
is removing and reestablishing replication my only
solution?
Thanks
Emma
Open a support case with PSS.
There is no such thing as a pending replicated transaction with merge as it
does not use the tran log. There is also no such thing as a log reader with
merge either. Sp_repldone won't do anything since it operates against the
distribution database and will have no effect with merge since merge doesn't
use the distribution database for transactions.
You have something else going on and if you don't have transactional
replication configured, you did at some point and something has been left
hanging.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||What happens if you stop and start SQL Server, and then try to truncate the
log?
"Emma" <eeemore@.hotmail.com> wrote in message
news:791d01c4312f$425272a0$a501280a@.phx.gbl...
> I am still struguling with shrinking a log file that is
> almost 5GB. The database is being replicated using merge
> replication. I ran the following commands without any
> success
> backup log Main_DB with truncate_only
> The log was not truncated because records at the
> beginning of the log are pending replication. Ensure the
> Log Reader Agent is running or use sp_repldone to mark
> transactions as distributed.
>
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
> Server: Msg 18757, Level 16, State 1, Procedure
> sp_repldone, Line 1
> The database is not published.
>
> dbcc opentran
> Transaction information for database 'Main_DB'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (305:22434:1)
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
>
> How do I go about shrinking/truncating the log file
> without having to remove replication from the database or
> is removing and reestablishing replication my only
> solution?
> Thanks
> Emma
|||Hilary,
The same thing happens when I stop and restart the
service.
Emma

>--Original Message--
>What happens if you stop and start SQL Server, and then
try to truncate the[vbcol=seagreen]
>log?
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:791d01c4312f$425272a0$a501280a@.phx.gbl...
merge[vbcol=seagreen]
the[vbcol=seagreen]
messages,[vbcol=seagreen]
or
>
>.
>
|||Michael,
What is PSS and how do I contact them?
Thanks
Emma

>--Original Message--
>Open a support case with PSS.
>There is no such thing as a pending replicated
transaction with merge as it
>does not use the tran log. There is also no such thing
as a log reader with
>merge either. Sp_repldone won't do anything since it
operates against the
>distribution database and will have no effect with merge
since merge doesn't
>use the distribution database for transactions.
>You have something else going on and if you don't have
transactional
>replication configured, you did at some point and
something has been left
>hanging.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||Emma,
this is the relevant webpage:
http://support.microsoft.com/default.aspx?scid=fh;en-
us;Prodoffer41a&sd=MVP
Regards,
Paul
|||I setup a standby/test server with the database and
removed replication completely and recreated it and I was
able to shrink the log file. There must have been a
problem in the original setup of the database or from the
applications accessing the database living transactions
open. I will go through the process again and document
everything I do, and hope it works on the production
server.
I will have to find the appropriate time to try this on
the production server as well. It will be a pain doing
this on the production server because there are about 12
publications there. I do not want to script the
publications because if there is an error in the original
setup, it may be carried over.
Thanks for all your help.
Emma
|||Hi all,
I had a similar problem but seemed to solve it by backing up the database first; the documentation suggests the log needs to be checkpointed and that occurs on a backup. I did this through the Query Analyzer; can't vouch that this works via Enterprise Manager or SQLDMO.
/* Backup */
use master
exec sp_addumpdevice 'disk', 'databak', 'c:\databak.dat'
exec sp_addumpdevice 'disk', 'logbak', 'c:\logbak.dat'
backup database target to databak
backup log target to logbak
/* Shrink log */
use target
declare @.fid int
select @.fid = File_ID('target_log')
dbcc shrinkfile (@.fid)|||Hi everyone,
I got a solution to this issue. I got stuck with this problem when I restored a database from a production server to a development server. On Production server this database was being used in replication also.
I restored this database from production server to development server and after that I wanted to truncate the log file of it on production server.
When I executed this query:
Backup Log <MyDatabaseName> With Truncate_Only
I got this nice message:
The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.
I used sp_repldone then I got the message:
Database is not published.
DBCC Shrinkfile also did not work.
Then I tried a trick, and guys it worked. What I did, I am writing in steps:
1. I published this database using the following query.
Execute SP_ReplicationDbOption <MyDatabaseName>,Publish,True,1
Here 1 is used for the parameter @.ignore_distributor, which can be 0 or 1.
If it set to 0 then this stored procedure will try to connect to ditributor database and update it with the new status of publication database. I used '1' because as I told earlier I restored my database on development server and so there was no distribution database.
2. Then I used that so called 'SP_Repldone' to mark the logs as distributed as given below:
Execute sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time = 0, @.reset = 1
3. Then I used DBCC ShrinkFile(<MydatabaseLogFileName>,0)
The log was shrinked successfully.
So, this was all the happy story guys.
sujeet4u <s.sp007@.yahoo.co.in>