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
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.
To shrink a data file or log file you have to issue a checkpoint first. What version of SQL Server are you using?
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.
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.
Thanks TRAD.... for your response..... Will try and come back to you for further help....
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.
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 %...
Your options are: 1. Delete data 2. Compress data 3. make sure all tables have primary keys
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.
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...
