?
Solved

Unable to shirk sql database

Posted on 2008-11-02
4
Medium Priority
?
7,227 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 44

Accepted Solution

by:
zephyr_hex (Megan) earned 1500 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Pimping Sharepoint 2007 without Server-Side Code Part 1 One of my biggest frustrations with Sharepoint 2007 in the corporate world is that while good-intentioned managers lock down the more interesting capabilities of Sharepoint programming in…
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses
Course of the Month8 days, 23 hours left to enroll

621 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