Can someone tell me if there is a way to shrink the log without having to restart the SQL service. I tried with DBCC SHRINKFILE and DBCC SHRINKDATABASE commands, but until I restart the SQL Service, it doesn't get reflected in the shrunk size. It is on the live server, so unless a scheduled maintenance notice is sent, we cannot do that, and as of now it can't be sent now. Need 2 days atleast.

Is there a way to achieve that?
Thanks,


-Vijay G

__________________
- Vijay G

Hi Vijay G,

I am not very good at this, so I may be giving you a very stupid idea, but I think you could do a backup of your database log with WITH TRUNCATE_ONLY.

Something like this:

BACKUP LOG YourDatabase
WITH
TRUNCATE_ONLY

Check out mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adminsql.chm::/ad_bkprst_565v.htm (couldn't find the online link) before you try any of this. And you may also want to try it in a testing environment first..... ;)

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks for the reply Imar.

Before posting my question here, I did try that to0. sorry to not have mentioned that in my previous post.

But I found "The command(s) completed successfully." alone as the output, and the size was not reduced too. After googling, I found this link

http://gensystem.europe.webmatrixhos...icles/171.aspx

and tried all the steps as mentioned there. Still no luck. Thought someone might give me a better solution.:) and posted here.

Jeff Mason is not seen posting much now a days. Was expecting his answer too on this

Let me too some R&D a lot and find something. Will keep you posted if I find something.

Cheers!

-Vijay G

I think that afterwards, you can use the EM to shrink your log file.

1. Open up the Enterprise Manager
2. Right-click your database and choose Shrink Database
3. Click the Files button
4. Choose your log file and set Shrink File to to the desired size.

I think then when a log file grows, also its "registered size" grows. So, even when you truncate, it doesn't get much smaller than this size. However, because the log has been truncated, you can now change its size using the Shrink dialog. I think there is also a SQL command for that available.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I am extremely sorry about not mentioning that mine was SQL 7.0

Hope the one you mentioned should be of SQL2000. Sorry to trouble you.

-Vijay G

Yes, you are right, but that effects/shows in the FILESIZE physically only when the SQL service is restarted or server rebooted.

DBCC SHRINKFILE(MYDB_Log, 500)
500 in MBs. It should be multiples of MBs only, cannot be 525 or so.

-Vijay G

Ah, right. Those are 2000 only options then? Hmmmm. With the UI I was able to change the log file size on a test database here, and it did so right away.

For 7.0, you may want to take a look at this article:

http://support.microsoft.com/default...b;EN-US;256650

Another thing you could do is detach the database. This way, the database still needs to be off line, but you don't have to stop SQL Server....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Yes Imar, You are right. I was just going through an article on taking it offline and getting there.

Quickly Shrinking the Transaction Log

Shrinking Active Log Files—Revisited

Will have to go as per that. But since it is a live server, I was trying to achieve that without taking it offline or so. Seems can't get there without doing that.

As my colleagues wanted me to do something on this to I was curious to sort this out, but anyways scheduled maintenance has been planned for coming sunday. Let me wait and got to monitor this closely until then.

Actually this is an internet mobile messaging database, that operates all the 24 hours.

Thanks a lot for the replies.
Cheers!

-Vijay G

Hmmmm, I was hoping that taking it off-line was just a quick hack, but now it seems like the only (??) way to do it....

On a 24/7 system, taking them off-line is not much fun ;)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar,
I did that finally. Anyways the SQL service restart is scheduled for sunday.;)

Actually I took a go ahead and got to do that a little while back and I am through. Here is what I did.

Steps for Shrinking LOG space

1) 'Removes the inactive part of the LOG without making a backup copy of it and truncates the log
    BACKUP LOG MY_DB WITH TRUNCATE_ONLY

2) 'Shrinks the size of the specified DATA file for the related database.
    DBCC SHRINKFILE (MY_DB_Log, 500)

3) 'Shrinks the size of the data files in the specified database.
    ' Not actually recommended for shrinking log file, but I wanted to check if the DB can also be
    ' shrinked, without having to restart SQL service. But no EFFECT
    DBCC SHRINKDATABASE (MY_DB, 30)

4) 'To bring DB offline and get the shrunk LogFile size effected physically in the HDD
    sp_detach_db MY_DB, true

5) 'To bring the DB online.
    sp_attach_db @dbname = N'MY_DB',
            @filename1 = N'F:\MSSQL7\Data\MY_DB_Data.MDF',
            @filename2 = N'F:\MSSQL7\Data\MY_DB_Log.LDF'

For some reasons, I have renamed my Live db name as MY_DB in the above commands. Actually I did that in less than 45 seconds. I tried this all in my production environment and kept things ready and was really fast to bring that back online.

May be some of my steps given above could have been redundantly done. Someone could correct me if I was wrong.

Thanks for all the support.
Cheers!

-Vijay G