Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 12th, 2004, 05:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default Shrink Log without SQL service restart.

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
  #2 (permalink)  
Old May 12th, 2004, 06:36 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #3 (permalink)  
Old May 12th, 2004, 07:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #4 (permalink)  
Old May 12th, 2004, 07:27 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #5 (permalink)  
Old May 12th, 2004, 07:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #6 (permalink)  
Old May 12th, 2004, 07:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:I think there is also a SQL command for that available
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
  #7 (permalink)  
Old May 12th, 2004, 07:51 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #8 (permalink)  
Old May 12th, 2004, 08:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #9 (permalink)  
Old May 12th, 2004, 08:08 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #10 (permalink)  
Old May 12th, 2004, 09:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to shrink data files in SQL Server 2000 chintan SQL Server 2000 6 October 15th, 2008 01:58 PM
how to restart the windows indexing service catalo hyder_master ASP.NET 2.0 Professional 0 January 18th, 2008 03:18 AM
restart MSSQL service from command line in windows crmpicco SQL Server 2000 4 January 25th, 2006 11:06 AM
Log Shrink acko SQL Server 2000 1 March 25th, 2004 10:57 AM
Shrink Trans Log Walden SQL Server 2000 2 November 3rd, 2003 09:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.