Shrinking a Database
-
Hi,
Apologies now, but I'm not a DBA, I'm a developer and we don't have any of the former here (budgetary constraints :ermm: )
Basically the question is a simple one, I have been working on a database (2005) and changing an old process into a new more efficient process. This has allowed me to truncate a bunch of now redundant tables to free up some space.
I have managed to reduce the database size from 350 GB to 28GB :w00t: , but the MDF file is still taking up the full quota of space. Unfortunately the database is "always on" running 24hrs a day while its processing new transactions (about 800,000 a day).
I tried to get away with just shrinking the database files, but to no affect. I assume the resolution is to shrink the database itself, but I am concerned about how long this would take and the impact it would have on the system.
Has anyone got any idea how long this would take? I was hoping that as it is now only 28GB, it would be quicker than a 350GB database, but I gave up second guessing SQL a long time ago...
Thanks in advance,
Tom
-
you can do it allwithin the GUI;
note on my second screenshot by shrinking the log of my "Sandbox" database, I'm freeing up a huge amount of space by shrinking it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Tom West (3/25/2010)
Hi,Apologies now, but I'm not a DBA, I'm a developer and we don't have any of the former here (budgetary constraints :ermm: )
Basically the question is a simple one, I have been working on a database (2005) and changing an old process into a new more efficient process. This has allowed me to truncate a bunch of now redundant tables to free up some space.
I have managed to reduce the database size from 350 GB to 28GB :w00t: , but the MDF file is still taking up the full quota of space. Unfortunately the database is "always on" running 24hrs a day while its processing new transactions (about 800,000 a day).
I tried to get away with just shrinking the database files, but to no affect. I assume the resolution is to shrink the database itself, but I am concerned about how long this would take and the impact it would have on the system.
Has anyone got any idea how long this would take? I was hoping that as it is now only 28GB, it would be quicker than a 350GB database, but I gave up second guessing SQL a long time ago...
Thanks in advance,
Tom
The answer on the timing is it depends.
I would not shrink the database down to the minimum 28GB. I would probably only shrink to 50GB or so. Once you shrink you will need to defrag the indexes.
What was the command you used when attempting to shrink the files?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
The unused space can be freed up and returned to the O.S. by shrinking each of the database files as below. Using SHRINKFILE is better than using SHRINKDATABASE.
USE <target_db>;
GO
-- Run below against a database file
-- leaving 10 GB total space for this file.
DBCC SHRINKFILE (<logical_db_file_name>, 10000);
GO
Repeat the shrinkfile for each database file with proper space.
-
Tom West (3/25/2010)
Hi,Apologies now, but I'm not a DBA, I'm a developer and we don't have any of the former here (budgetary constraints :ermm: )
Basically the question is a simple one, I have been working on a database (2005) and changing an old process into a new more efficient process. This has allowed me to truncate a bunch of now redundant tables to free up some space.
I have managed to reduce the database size from 350 GB to 28GB :w00t: , but the MDF file is still taking up the full quota of space. Unfortunately the database is "always on" running 24hrs a day while its processing new transactions (about 800,000 a day).
I tried to get away with just shrinking the database files, but to no affect. I assume the resolution is to shrink the database itself, but I am concerned about how long this would take and the impact it would have on the system.
Has anyone got any idea how long this would take? I was hoping that as it is now only 28GB, it would be quicker than a 350GB database, but I gave up second guessing SQL a long time ago...
Thanks in advance,
Tom
You have to truncate mdf file to release un used space to OS.
From BOL
NOTRUNCATE
Compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file. target_percent is optional.
The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the database appears not to shrink.
NOTRUNCATE is applicable only to data files. The log files are not affected.
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_percent is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files. The log files are not affected.
EnjoY! -
GTR (3/26/2010)
Tom West (3/25/2010)
Hi,Apologies now, but I'm not a DBA, I'm a developer and we don't have any of the former here (budgetary constraints :ermm: )
Basically the question is a simple one, I have been working on a database (2005) and changing an old process into a new more efficient process. This has allowed me to truncate a bunch of now redundant tables to free up some space.
I have managed to reduce the database size from 350 GB to 28GB :w00t: , but the MDF file is still taking up the full quota of space. Unfortunately the database is "always on" running 24hrs a day while its processing new transactions (about 800,000 a day).
I tried to get away with just shrinking the database files, but to no affect. I assume the resolution is to shrink the database itself, but I am concerned about how long this would take and the impact it would have on the system.
Has anyone got any idea how long this would take? I was hoping that as it is now only 28GB, it would be quicker than a 350GB database, but I gave up second guessing SQL a long time ago...
Thanks in advance,
Tom
You have to truncate mdf file to release un used space to OS.
From BOL
NOTRUNCATE
Compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file. target_percent is optional.
The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the database appears not to shrink.
NOTRUNCATE is applicable only to data files. The log files are not affected.
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_percent is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files. The log files are not affected.
Be careful of using the TruncateOnly command. If the last allocated extent is towards the end of the file you will not release much free space at all.
It is better to not use either of those commands when trying to shrink the file.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply