Database logfile showing red

[Former Member]

Hello All,

We are using SAP 4.7 & OS is windows 2003 & database is Microsoft SQL 2003.

SAP mdf and ldf  files (data and log databases) have grown to a huge size

which is using up too much space and could make the system unstable.

I am attaching screen shot for reference.

Please suggest me what to do?

Thanks & Regards,

Mahendra

[former_member212248]

Things to try ...

1)  Put database into SIMPLE recovery mode

2)  CHECKPOINT database

3)  Return database back to FULL recovery mode

4)  BACKUP database

5)  SHRINK the transaction log

[Former Member]

Here is what you need to do.

Prod

- At the SQL level, for the LDF file, increase the max file size using Management Studio. Make sure the max size is less than the size of your t-log disk. also as best practice, change autogrowth to a higher value. like 2-3GB. You will have high number of VLFs in your t-log if you give autogrowth to 60MB which will dramatically increase the DB Startup/recovery time.

For Dev

- Seems you have no space in the file, Change the autogrowth to 2-3GB instead of 60MB. with only 60MB autogrowth, your file status will mostly be red. Also manually increase the file whenever you see less than 10-15% of free space in a Datafile. Else you will see performance issues whenever there is a autogrowth. Keep the Datafiles in separate drives. if you cannot, make sure the free space in the drive is always 20% more than the size of data in the drive.

[Former Member]

Hello everyone,

Thanks for your help & suggestions.

Regards,

Mahendra

[Former Member]

Hello all,

Yesterday I have posted screen shots which are of  production server.

Now I am attaching screen shot of development server in which all data files are in RED COLOR, & log file is in green color.

Please tell me what to do in this case?

Can I shrink data files? If yes, upto what limit & can I do it when database is online?

Thanks & Regards,

Mahendra

[Former Member]

Hi,

Have you deleted any data from the sap system ?

If no then you need to get more space in the drive.

Thanks

Rishi Abrol

[Former Member]

Hi Rishi,

I didn't delete anything.

Can't I shrink all datafiles?

Thanks & Regards,

Mahendra

[Former Member]

Hi,

If you have not deleted the data in the database how will shrink help you.

Another think log file should not be larger than data file .

Thanks

Rishi Abrol

[Former Member]

Hi Rishi,

Thanks for your reply.

In this case what shall i do?

[Former Member]

Hi,

Please try to either add more space in the drive. Another thing try to reduce the log or move it to new drive. You can see the blog how to reduce the log size.

Thanks

Rishi abrol

[Sriram2009]

Hi Patil

Could you check the SQL log which is located in the same page. Its showing any error messages?

Thanks

Sriram

[Sriram2009]

Hi Mahendra

1. In MS Sql DB Data & log files are in the same drive Q: you have to move the either log or data file to different drive letter. Kindly refer the link http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

2. In the log file you have given the size limit 30000 Mb you have to give unlimited disk space same like data file

3. During the off peak time just run the MS SQL DB check by using the command ' DBCC CheckDB' refer the link http://technet.microsoft.com/en-us/library/ms176064.aspx , Refer the SAP Note 142731 - DBCC checks of SQL server

4. Apply the latest service pack & Patches on MS Sql 2005 server

Thanks

Sriram

[Former Member]

Hi,

Is there any huge action performed on the server. MDF is the actual data in the server. have you deleted the data from the database and you ant to not free up the space that was used?

I have see this in the PCM space where large model cause this.

Database Administrator to get the two database files (.MDF and .LDF files) down to a manageable size.Performing maintenance on the SQL Server database instances to truncate the Transaction Log file. Shrinking the database files to their minimum possible size after having truncated the log file.

Please read the below note .

Note 363018 - File management for SQL Server

Please read the below blog.

https://scn.sap.com/thread/2097242

http://www.packtpub.com/article/sql-server-2012-implementing-physical-database-strusture

Thanks

RishIA brol

[Former Member]

Check your SQL Server logs and event logs of your Database Server.

[former_member184473]

Hello Mahendra,

If you scroll to the right, what is mentioned in the column "Status Message"?

Regards,

Eduardo Rezende

[Former Member]

Hello Eduardo,

I am attaching screen shot for your reference.

Thanks & Regards,

Mahendra