Solved

Shrink A database in SQL

Posted on 2016-08-10
16
83 Views
Last Modified: 2016-08-11
I have an SQL server  2008 r2 with 300 gigs of space however, I'm down to 77 gigs (yikes).  The particular database currently is 150875.06 MB I went to the properties and shrink database and I'm running this but it's been running for over an hour.  I'm not sure if this is correct.  Is there another way for me to get some room.  I already have the options set for recovery model simple on all the 10 database partitions.  I'm not a DBA by any means and there are 10 partitions already. the main database is the largest and the one I'm trying to shrink. I'm using SQL Management Studio for this task.  Is there a better, faster way?
0
Comment
Question by:WellingtonIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 10

Expert Comment

by:Damjan
ID: 41750840
Don't shrink your databases to free up disk pace.  It's a bad idea. It is generally known as a worst practice to ever shrink a production database or data file...

Follow these tips to reclaim disk space
https://www.mssqltips.com/sqlservertip/1810/out-of-space-on-the-c-drive-of-your-sql-server-and-ways-to-reclaim-disk-space/

Maybe you could just create a new database and migrate data to it.
0
 

Author Comment

by:WellingtonIS
ID: 41750846
Can I stop it in the middle?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41750853
Shrinking can only help if you have significant amounts of unused space.

You also need to distinguish between data space and log space.  Shrinking a vastly over-sized log can make sense any time.  Shrinking data is very rarely done.

To check for unused data space, run this command:

USE <your_db_name>
DBCC SHOWFILESTATS /*this command does NOT list log space*/
If the "UsedExtents" are significantly lower than the "TotalExtents", it might make sense to shrink the file.

YOU SHOULD ALWAYS SHRINK FILE BY FILE, NEVER THE ENTIRE DATABASE.

You can check all file sizes, including the log file, like so:
EXEC sp_helpfile
If the log file is extremely large/oversized, you can shrink the log file directly.
DBCC SHRINKFILE(2, <total_mb_to_remain_in_log_file>)
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41750857
Yes, you can safely cancel a shrink.
0
 

Author Comment

by:WellingtonIS
ID: 41750871
I ran EXEC sp_helpfile but I do not see any sizes for logs?  As for deleting the logs manually I have no idea where they are
sql.png
0
 

Author Comment

by:WellingtonIS
ID: 41750881
I ran this and it didn't do anything EXEC sp_cycle_errorlog
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 41750904
EXEC sp_helpfile /*not sp_help*/
0
 

Author Comment

by:WellingtonIS
ID: 41750907
Now how do I shrink the logs? Is there a command?  The logs are about 102400 KB for all 10 but on my master database I have 37712192 KB
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41750920
ALTER DATABASE master SET RECOVERY SIMPLE;
USE master;
DBCC SHRINKFILE(2, 2048);

Check msdb and tempdb log sizes as well.
0
 

Author Comment

by:WellingtonIS
ID: 41750930
it's executing.  I hope this works.  I replaced master with the name of the database - great I reclaimed 133 gigs of space.  thanks you rock! :)  I'll need to save this so I can use it again.
0
 

Author Closing Comment

by:WellingtonIS
ID: 41750932
Thanks.  This really helped.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41750941
Really glad it helped! ...

... But, for a user db, you want to go back and allocate additional space, since 2GB is probably not enough, especially if the allocation was over 100GB before.

First run these commands:

USE [db_name]
EXEC sp_helpfile
--Copy/save the name in the first column for file#2, the log file.

Then run these commands to gradually increase the log size (yes, there are technical reasons for not going to the total size in one command):
ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 6GB )
ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 10GB )
ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 14GB )
ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 18GB )
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 41751108
Since it doesn't seem to have been mentioned yet ...

Make sure you're backing up your transaction log frequently so that it doesn't need to grow and grow.

A transaction log file will grow forever when your database is in full recovery mode if you're not making transaction log backups.
0
 

Author Comment

by:WellingtonIS
ID: 41751905
Actually this database is for Websense and I can detach the older DB's from 2015 so that's going to be my next step.  I don't need to hold information on web activity for years. 6 months is more than enough

USE [db_name]
 EXEC sp_helpfile
 --Copy/save the name in the first column for file#2, the log file.

 Then run these commands to gradually increase the log size (yes, there are technical reasons for not going to the total size in one command):
 ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 6GB )
 ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 10GB )
 ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 14GB )
 ALTER DATABASE [db_name] MODIFY FILE ( NAME = <copied/saved name from above>, SIZE = 18GB )

Does this equal the logfile name?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41752441
Yes, the logical log file name, not the physical file name.  You'll see the logical name in the first column in the output from "exec sp_helpfile".  The log file will be file#2.
0
 

Author Comment

by:WellingtonIS
ID: 41752452
OK thanks.  I got some serious room back.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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