Solved

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

Posted on 2011-09-19
7
435 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 142

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 142

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL HELP 2 87
Isolation level in SQL server 3 49
Table create permissions on SQL Server 2005 9 41
Need a SQL query that creates a header row and one or more detail rows. 7 31
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
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…

776 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