September 6, 2012 at 4:37 am
Hi All,
My Production DB file has following size
MDF file: 40 GB
LDF File: 80 GB
Now, I don't have any available space in my production server and I was told to re-use the space which was occupied by LDF file (80 GB). When googling found that, Shrinking log file is one of the option to get back the space from log file. Also found that, Shrinking log file would leads fragmentation issue and it affect performance of application.
How can we reduce the space without shrinking or Let me know any other approach need to be follow here?
Thanks.
September 6, 2012 at 4:43 am
ok - can you run the command
sp_helpdb
against your server and find out what the recovery mode is on that database (its either simple, full or bulk logged)
then run the following
dbcc sqlperf(logspace)
post the results here (just for that database) and then we can help you
also is this a production server? or development... do you use log shipping or mirroring ? or replication ?
MVDBA
September 6, 2012 at 4:45 am
You need to understand why the log file grew to the size it is. If it was through normal database activity, then there's no use shrinking since it will only grow back to the same size. If it happened because of a one-off event (for example a data load) then it's probably acceptable to shrink to a size that you don't expect it to grow beyond.
John
September 6, 2012 at 4:46 am
Shrinking log file would leads fragmentation issue and it affect performance of application.
i think somebody is giving you missinformation - shrinking the data file will give you fragmentation..... shrinking the log file won't (in simple speak) .. the main reason for not shrinking the log is because it will proboably be needed again and have to grow anyway... but it sounds like you have the wrong recovery mode , are not backing up the logs, or replication or mirroring has failed and you don't know about it (if you use it)
MVDBA
September 6, 2012 at 4:57 am
Hi MVDBA,
Recovery Mode: Full
dbcc sqlperf(logspace) Command output:
Log Size(MB) - 1917.742
Log Space Used (%) - 12.87614
Now, I don't have access to my Production DB, I have taken above said size from our staging server. We have the same problem here too...
September 6, 2012 at 5:02 am
MVDBA (9/6/2012)
shrinking the data file will give you fragmentation..... shrinking the log file won't
Yes, it will - it is likely to cause physical fragmentation on the disk (at least it will when the file grows back to its original size). But you're right - we don't have enough information yet to sort this out. We know that we're in full recovery mode, but we don't know whether or how often the log is being backed up, nor whether there is anything that may delay truncation of the log, such as replication, mirroring or log shipping.
John
September 6, 2012 at 5:10 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2012 at 5:10 am
We have not taken up the log file back up or truncation of log file so far.
We just leave the database as it is since it was created...:crying:
September 6, 2012 at 5:11 am
hold on you said the log file was 80GB????
MVDBA
September 6, 2012 at 5:15 am
Hi MVDBA,
Yes, My Production log file size is 80 GB..
But now, I don't have access to run dbcc sqlperf(logspace) command in Prod. Server..
So, I've taken the log file size from our staging server...We have the same problem here too..
September 6, 2012 at 5:19 am
whizkidgps (9/6/2012)
We have not taken up the log file back up or truncation of log file so far.We just leave the database as it is since it was created...:crying:
You definitely need to read through that article I just referenced. Get your DBA to read it too (I assume you're not the DBA if you don't have permissions to run a DBCC statement)
This might help too. http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2012 at 5:35 am
sorry chap , that means the information you have posted is useless... if you don't have access to the server then how are you going to fix it anyway ?
or provide the info to the people on the forums so that they can give you the correct advice
MVDBA
September 6, 2012 at 5:56 am
Hi MVDBA,
I'll get back the access from my client end on tomorrow. Due to network issue, I'm not able to access now..
Can you help me out of this space issue problem in production?
September 6, 2012 at 5:58 am
Before you do anything else...
It's not a space issue, it's a complete lack of log management.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2012 at 5:59 am
simple
2 options
shrink the log or get more disk space (possibly by moving the log or data files) - both of which need access to the server.
MVDBA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply