February 1, 2010 at 2:53 am
I am having a problem shrinking the log on our production server, unfortunately this has to be done today during normal working hours.
When I try to shrink the file from 6000MB to 3000MB to free up space to the OS I get the message that DBCC SHRINKFILE did not work as the file is in use.
I have checked there were no open transactions before running using DBCC OPENTRAN and lastly the actual log space used is on approx 200MB.
Any ideas?
Or do I need to wait until tonight till the system is not is use?
MCITP SQL 2005, MCSA SQL 2012
February 1, 2010 at 3:29 am
hi
is this a published database ?
Because we have the same issues with a published database
Kind regards
Jef
JV
February 1, 2010 at 3:34 am
Hi there,
First take a look at http://qa.sqlservercentral.com/Forums/Topic849184-146-2.aspx
then in BOL http://msdn.microsoft.com/en-us/library/ms189493.aspx the description for DBCC SHRINKFILE says it allows other users to be working with the DataBase.
The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run the instance of SQL Server in single-user mode to shrink the system databases.
José Cruz
February 1, 2010 at 3:40 am
The first question that should be asked is – why do you want to shrink the log? Are you sure that it should be done. Unfortunately there are many administrators that shrink the logs on a regular basis. If you didn’t run a onetime task that increased the log and you know that the log doesn’t need to be at its current size, then it is O.K to shrink it. If however you are shrinking the log on a regular base (for example every week), then you should stop doing so. In that case the log needs this space and it will reclaim it later on, so there is no need to shrink it at all.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 1, 2010 at 5:52 am
The database was upgraded over the weekend by a member of our support staff, which caused the log to grow by 5GB while all the database changes occured.
Usually the log stays below the 1GB mark during normal production running, it was supposed to have been shrunk back down to 3GB after the upgrade however the member of staff forgot.
I know I shouldn't have passed the work on and should have done it myself but I had family commitments I couldn't get out of.
Thanks for the links above I'll give it a try and get back to you all later.
MCITP SQL 2005, MCSA SQL 2012
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply