Solved

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

Posted on 2011-09-19
7
438 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

791 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