Shrink Database/Files

Hello DBA's

I have a problem... One of my database files both data and log file is huge and when i am trying to shrink the files and database it's not shrinking since space available is 0 %.

Please advice me how to get the space in file system during this type of problem.

Thanks Leslie Richards
more ▼

asked Aug 29, 2011 at 02:00 PM in Default

Leslie Richards gravatar image

Leslie Richards
11 1 1 1

I just blogged about when and how to shrink transaction logs. Maybe it will help a bit.
Aug 30, 2011 at 10:04 PM Tim
(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest
To shrink a data file or log file you have to issue a checkpoint first. What version of SQL Server are you using?
more ▼

answered Aug 29, 2011 at 02:01 PM

Tim gravatar image

36.4k 38 41 139

Thanks for the response.... SQL 2005 Enterprise Edition...
Aug 29, 2011 at 02:03 PM Leslie Richards
There are several ways to approach this. First if you could take a backup of your log then issue a shrink you should be good, or you could backup the log with truncate_only then immediately shrink the log if you don't have a need for the contents of the log. With the data file it sounds like the file is full and there is no free space to reclaim. If that is the case then you would need to delete space in order to reduce the size, likely not the case.
Aug 29, 2011 at 02:07 PM Tim
Thanks TRAD.... for your response..... Will try and come back to you for further help....
Aug 29, 2011 at 02:21 PM Leslie Richards
Sure, if your data file is 100% full there is no free space to reclaim by shrinking the file. The only way to reclaim any space would be to free up space by deleting data that is not needed. Things like old staging tables, etc. This is most likely not something you can do if the database is well managed. It really seems that your log file has grown due to not taking regular transaction log backups and truncating the log, shrinking it, then taking regular backups of your transaction log will take care of your needs. Most of my production servers I am taking transaction log backups every 15 minutes.
Aug 29, 2011 at 02:38 PM Tim
Hey! Well done on cross 20K
Aug 30, 2011 at 05:07 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

OK. Huge log file would indicate either a massive amount of transactional activity, or no transaction log backups have been scheduled. I go with the latter.

Make sure that, as well as doing a "Full" or "Differential" backup, you're also doing a "Transaction Log" backup. Schedule this to run, well, according to how much data you can afford to lose... If it helps, I have some servers that back up TLogs every 15 minutes, and others that back up TLogs every hour. And some (non-production) servers that have all databases set to "SIMPLE" recovery mode which remove the need for transaction log backups.

OK, this is a slightly elderly post, but it's a good starting point... http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/

Once you've corrected the lack-of-transaction-log-backup issue, you may find you need to wait a little while before you can successfully shrink the transaction log file.
more ▼

answered Aug 29, 2011 at 02:33 PM

ThomasRushton gravatar image

ThomasRushton ♦
34.1k 18 20 44

Thanks for the post thomas... the point which you have mentioned is already configured and running.....

I were able to retrieve space from log files... can you please tell me how to get space from data files... if the available space is 0 %...
Aug 29, 2011 at 02:38 PM Leslie Richards

Your options are:

  1. Delete data
  2. Compress data
3. make sure all tables have primary keys
Aug 29, 2011 at 02:41 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

Is the hard drive completely full where the data files are stored? You say the data files are full but that might be simply because autogrowth is turned off. If there is drive space then you could grow the data files a little to get you through this time while you review and fix the situation.

Do you have alternative hardware that some of these databases can be moved to?

You need to meet with hardware team and system managers and get the needs of the company matching up with the capabilities of the hardware and then get busy in the middle making sure the data is kept safe and secure.

You need to review each database and consider its recovery model, DR/BC plans, backup strategy and growth predictions for the next 12 months. Once you have that then you can start to plan and monitor the databases and adjust your plans/predictions accordingly.
more ▼

answered Aug 29, 2011 at 03:44 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Hello jack... for the respond.... actually drive is full and when i am trying to shrink the data file which is the huge in the drive... when i am trying to shrink it... its not shrinking... since space available is 0 % Please help me how to proceed from here.... Other DBA's told some options.... but i dont think deleting the old tables or unwanted data from data file is a easy task... as we dont have any connection or awareness what is loaded into data files...
Aug 30, 2011 at 10:23 AM Leslie Richards
If your data file is full and your disk is full your option is to detach the database, move it to a larger capacity drive and reattach, or if your drive is a dynamically provisioned disk from a SAN have them expand it.
Aug 30, 2011 at 10:44 AM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 29, 2011 at 02:00 PM

Seen: 1323 times

Last Updated: Aug 29, 2011 at 02:00 PM