June 23, 2011 at 4:35 am
Hi All,
I have an SQL 2000 Database which is 135GB and I want to perform "Shrink Database" but I want to know what is the maximum percentage that I can shrink it to please.
Thank you
June 23, 2011 at 4:45 am
It's not about percentages - it's about how much free space you have in your database. There are different rules for how much space will be released from data files and log files. Have a read of the BDCC SHRINKFILE topic in Books Online and post back if there's anything you don't understand.
John
June 23, 2011 at 4:58 am
tt-615680 (6/23/2011)
Hi All,I have an SQL 2000 Database which is 135GB and I want to perform "Shrink Database" but I want to know what is the maximum percentage that I can shrink it to please.
Thank you
First why do you want to do this?
You are not supposed to shrink your database it will increase the fragmentation.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 23, 2011 at 5:10 am
muthukkumaran Kaliyamoorthy (6/23/2011)
tt-615680 (6/23/2011)
Hi All,I have an SQL 2000 Database which is 135GB and I want to perform "Shrink Database" but I want to know what is the maximum percentage that I can shrink it to please.
Thank you
First why do you want to do this?
You are not supposed to shrink your database it will increase the fragmentation.
The reason why I want to shrink the Database is because I currently only have 12GB left from 135GB. Or is it best to Defragment the Database?
Thank you
June 23, 2011 at 5:23 am
Is your database in Full recovery mode or Simple? If Full, how often do you back up your transaction log? How much of the 135GB is data file(s) and how much is log file(s)?
John
June 23, 2011 at 5:23 am
tt-615680 (6/23/2011)
muthukkumaran Kaliyamoorthy (6/23/2011)
tt-615680 (6/23/2011)
Hi All,I have an SQL 2000 Database which is 135GB and I want to perform "Shrink Database" but I want to know what is the maximum percentage that I can shrink it to please.
Thank you
First why do you want to do this?
You are not supposed to shrink your database it will increase the fragmentation.
The reason why I want to shrink the Database is because I currently only have 12GB left from 135GB. Or is it best to Defragment the Database?
Thank you
Nope the Defragment will not give you free space.
Your DB is 135 GB
What is the size of MDF & LDF ?
Do you have any other dirives with free space?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 23, 2011 at 5:30 am
John Mitchell-245523 (6/23/2011)
Is your database in Full recovery mode or Simple? If Full, how often do you back up your transaction log? How much of the 135GB is data file(s) and how much is log file(s)?John
John
You have spoken my mind. 🙂
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 23, 2011 at 6:36 am
John Mitchell-245523 (6/23/2011)
Is your database in Full recovery mode or Simple? If Full, how often do you back up your transaction log? How much of the 135GB is data file(s) and how much is log file(s)?John
Thank you for you reply, the recovery mode is set to simple. There are 4 different Database files (all in different filegroups), 2 PRIMARY file types which equal to 15.56GB and 2 log types which equal to 9MB.
June 23, 2011 at 6:39 am
tt-615680 (6/23/2011)
John Mitchell-245523 (6/23/2011)
Is your database in Full recovery mode or Simple? If Full, how often do you back up your transaction log? How much of the 135GB is data file(s) and how much is log file(s)?John
Thank you for you reply, the recovery mode is set to simple. There are 4 different Database files (all in different filegroups), 2 PRIMARY file types which equal to 15.56GB and 2 log types which equal to 9MB.
I must be missing something. I'm doing the math, and 2 data files that equal 15.56 GB plus 2 log files that equal 9 MB is only 16 GB of space being taken up by your databases.
If I'm reading this wrong, and *each* of the 2 data files is 15+ GB, that's still on ~30GB of your 135 GB of space.
What else is eating space on that drive?
-Ki
June 23, 2011 at 7:21 am
Kiara (6/23/2011)
tt-615680 (6/23/2011)
John Mitchell-245523 (6/23/2011)
Is your database in Full recovery mode or Simple? If Full, how often do you back up your transaction log? How much of the 135GB is data file(s) and how much is log file(s)?John
Thank you for you reply, the recovery mode is set to simple. There are 4 different Database files (all in different filegroups), 2 PRIMARY file types which equal to 15.56GB and 2 log types which equal to 9MB.
I must be missing something. I'm doing the math, and 2 data files that equal 15.56 GB plus 2 log files that equal 9 MB is only 16 GB of space being taken up by your databases.
If I'm reading this wrong, and *each* of the 2 data files is 15+ GB, that's still on ~30GB of your 135 GB of space.
What else is eating space on that drive?
No, both the PRIMARY files are equal to 15GB and that is what I am trying to get to, what is eating up the space.
June 23, 2011 at 7:35 am
OK, let's try this a different way. Please run this and post the results:
USE <InsertYourDBNameHere>
SELECT type_desc, name, physical_name, size * 8.0/1024/1024 AS file_size
FROM sys.database_files
Thanks
John
June 23, 2011 at 7:48 am
John Mitchell-245523 (6/23/2011)
OK, let's try this a different way. Please run this and post the results:
USE <InsertYourDBNameHere>SELECT type_desc, name, physical_name, size * 8.0/1024/1024 AS file_size
FROM sys.database_files
Thanks
John
Sys schema for sql 2000??? first time I ever heard of that ;-).
June 23, 2011 at 7:51 am
Ah yes, good shout. Try this:
USE <InsertYourDBNameHere>
SELECT groupid, name, filename, size * 8.0/1024/1024 AS file_size
FROM sysfiles
John
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply