Solved

Is there a way to shrink an sql database that has already reached the maximum size?

Posted on 2011-09-19
7
439 Views
Last Modified: 2012-05-12
is there a way to forcefully shrink a database that has already reached the maximum size of 4gb?
0
Comment
Question by:Petersennik
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36558988
Im guessing this is SQL 2005 Express. SQL 2008 Express allows for a 10gb database. Failing that, I would try using DBCC SHRINKFILE. That's about you're only option short of deleting records.
0
 
LVL 3

Expert Comment

by:chandrasekar1
ID: 36558996
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36559050
shrinking will only work if you can shrink (aka if there is unused space)

for data files, this means you would need to delete data from the tables
for log files, this means that you need to have regulare transaction log backup in place (presuming the db is in the FULL recovery mode)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 5

Expert Comment

by:AlokJain0412
ID: 36559053


Go to The Query Analyzer and Put Following command



1.

DBCC SHRINKDATABASE ('Your Database Name','Shrink Precentage in number)
Example
DBCC SHRINKDATABASE (UserDB, 10)

Or

2 The following example shrinks the data files in the database to the last allocated extent.
DBCC SHRINKDATABASE (UserDB, TRUNCATEONLY)


Go to Enterprise manager & make sure you restrict the file size of the database

if not solve then let me know again  Where you are getting message



0
 

Author Comment

by:Petersennik
ID: 36559494
i cant do a shrink as there isn't any unused space left in the database. I cant delete anything because the database is for my anti-virus which i now cant log into due to the database having reached maximum capacity
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36559716
then you have to upgrade the edition of the sql server.
0
 
LVL 5

Accepted Solution

by:
AlokJain0412 earned 500 total points
ID: 36559961
You hv to upgrade sql server

10GB is the Limit for SQL Server 2008 R2 Express and 4GB is the limit for the Express 2008.

You can try following  

Create disk space by deleting
unneeded files,
 dropping objects

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL page split per second is high 19 123
Set the max value for a column 7 41
Order by but want it in specific order 2 35
Need help in debugging a UDF results 7 52
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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