Solved

Unable to shirk sql database

Posted on 2008-11-02
4
6,893 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Microsoft SharePoint Foundation 2010 and Microsoft SharePoint Server 2010 do not offer the option to configure the location of the SharePoint diagnostic trace log files during installation.  This can, however, be configured through Central Administr…
Configuring network clients can be a chore, especially if there are a large number of them or a lot of itinerant users.  DHCP dynamically manages this process, much to the relief of users and administrators alike!
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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