question

Leslie Richards avatar image
Leslie Richards asked

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
shrink-database
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
I just [blogged]( http://timradney.com/2011/08/31/shrinking-transaction-logs/) about when and how to shrink transaction logs. Maybe it will help a bit.
0 Likes 0 ·
Tim avatar image
Tim answered
To shrink a data file or log file you have to issue a checkpoint first. What version of SQL Server are you using?
10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Hey! Well done on cross 20K
2 Likes 2 ·
Tim avatar image Tim commented ·
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.
1 Like 1 ·
Leslie Richards avatar image Leslie Richards commented ·
Thanks for the response.... SQL 2005 Enterprise Edition...
0 Likes 0 ·
Tim avatar image Tim commented ·
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.
0 Likes 0 ·
Leslie Richards avatar image Leslie Richards commented ·
Thanks TRAD.... for your response..... Will try and come back to you for further help....
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
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... 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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leslie Richards avatar image Leslie Richards commented ·
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 %...
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Your options are: 1. Delete data 2. Compress data 3. make sure all tables have primary keys
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
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.
2 Likes 2 ·
Leslie Richards avatar image Leslie Richards commented ·
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...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.