Solved

Unable to shirk sql database

Posted on 2008-11-02
4
6,923 Views
Last Modified: 2012-05-05
I am trying to shrink an sql database to make room on the c drive since it is running out o fspace. this is the error message that i get when i try to accomplish this. thank you and have a great day. I checked the logs on microsoft and there was nothing under user action.

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Shrink failed for Database 'WSS_Content_0953a0ce-9029-4c57-8dcf-ec1d4491692a'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
Comment
Question by:MRapacz
  • 2
4 Comments
 
LVL 9

Expert Comment

by:Sander Stad
ID: 22865130
What does the error say in the MS SQL Logfiles. Most of the time if a shrink doesn't work it is because there is a proces or an user that blocks the action.
0
 

Author Comment

by:MRapacz
ID: 22866552
this is the new error that generated while trying to shrink the database. looked at the microsoft recommendation and not really sure on where to find those two files.




TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Shrink failed for Database 'WSS_Content_0953a0ce-9029-4c57-8dcf-ec1d4491692a'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Lock request time out period exceeded.
Could not adjust the space allocation for file 'WSS_Content_0953a0ce-9029-4c57-8dcf-ec1d4491692a'.
Cannot shrink log file 2 (WSS_Content_0953a0ce-9029-4c57-8dcf-ec1d4491692a_log) because all logical log files are in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
0
 

Author Comment

by:MRapacz
ID: 22868485
OK so i got the database to shrink to 3gb space remaining. how do i keep it down. what is the next recommended step
0
 
LVL 43

Accepted Solution

by:
zephyr_hex (Megan) earned 500 total points
ID: 22872655
there are a couple of ways.

one way is to change the recovery model to simple.  however, you should be aware of the implications and how changing to a simple recovery model may limit your recovery options...
for a very simple overview:
http://www.networkworld.com/community/node/21486

another way is to configure auto shrink.  please know that auto shrink can cause performance issues... if it runs during peak times.  also, i'm not sure if this feature is available in the express version of sql.

all of these settings are available on the properties of the database.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I used to be SharePoint evangelist in our company, so my Outlook always full of questions about how to do this, or where I can find that. One day I found such an email with the following question: "how to attach 3-State workflow (one of the workflow…
SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question