September 29, 2011 at 10:21 am
I have a database which is 1.5 GB. Recently I deleted some data(one third data in the entire databases) in the database and truncated the transactional log after that.
But now my DB size is showing up as 3.1 GB. I dont see any major new data in the database after deleting 1/3 of the data.
What could be the reason and is there a way that I can reduce the the DB size.
Thanks.
September 29, 2011 at 10:27 am
September 29, 2011 at 10:33 am
When I click the properties of the database I see only 100MB free out of 3.2 GB.
Still Can shrink the mdf file?
Thanks.
September 29, 2011 at 10:33 am
If you are not running into storage space problems - leave it.
It is good to have free space in a database.
Shrinking can cause heavy fragmentation and degrade performance. Only shrink if absolutely necessary.
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
September 29, 2011 at 10:33 am
sql_novice_2007 (9/29/2011)
When I click the properties of the database I see only 100MB free out of 3.2 GB.Still Can shrink the mdf file?
Thanks.
Defrag and update stats. But don't shrink.
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
September 29, 2011 at 10:39 am
sql_novice_2007 (9/29/2011)
When I click the properties of the database I see only 100MB free out of 3.2 GB.Still Can shrink the mdf file?
Thanks.
Try executing to find and verify you results with it to see the excat size of the database.
SELECT
a.file_id,
LOGICAL_NAME = a.name,
PHYSICAL_FILENAME = a.physical_name,
FILEGROUP_NAME = b.name,
FILE_SIZE_GB = CONVERT(DECIMAL(12,2),ROUND(a.size/(128.000*1024),2)),
SPACE_USED_GB = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/(128.000*1024),2)),
FREE_SPACE_GB = CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/(128.000*1024),2))
FROM sys.database_files a LEFT OUTER JOIN sys.data_spaces b
ON a.data_space_id = b.data_space_id
September 29, 2011 at 11:30 am
It shows 150 MB free space out of 3.1 GB. Can I create any free space on it?
In fact I ran into space issue. Thats why I wanted to create some space on the server.
THanks.
September 29, 2011 at 11:34 am
3 GB is a tiny database. Defrag and update stats.
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
September 29, 2011 at 12:23 pm
are you running index maintenance plans\jobs at all, these can cause the database file to expand
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 29, 2011 at 12:56 pm
sql_novice_2007 (9/29/2011)
It shows 150 MB free space out of 3.1 GB. Can I create any free space on it?In fact I ran into space issue. Thats why I wanted to create some space on the server.
THanks.
If only 150 MB free space is shown than and as you said you data file is 1\3 rd of that then teh log file is the one taking space.
take a log backup to make avilable log file for other queries that clears the log space in first then see any free space released then again shrink it if you want more space.
AS Jason told Shrinking frags thats true but you can do ddefrag after releasing the space.
September 29, 2011 at 1:03 pm
@SQLFRNDZ (9/29/2011)
sql_novice_2007 (9/29/2011)
It shows 150 MB free space out of 3.1 GB. Can I create any free space on it?In fact I ran into space issue. Thats why I wanted to create some space on the server.
THanks.
If only 150 MB free space is shown than and as you said you data file is 1\3 rd of that then teh log file is the one taking space.
It is not entirely accurate that the log file is taking the space. He could have deleted 2/3 of his data from the data file but the data file may not show that it has been released until a defrag is done.
...
AS Jason told Shrinking frags thats true but you can do ddefrag after releasing the space.
If you shrink the data file and then you defrag - the data file will just grow again. I would defrag and look for something else on the drives to remove or move. 3GB is really a very small database.
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
September 29, 2011 at 1:07 pm
@SQLFRNDZ (9/29/2011)
take a log backup to make avilable log file for other queries that clears the log space in first then see any free space released then again shrink it if you want more space.
// Do I even want to start.....
Log backups do not, will not ever release space to the OS. Lack of log backups is not the only thing that can cause the log to grow. Investigation should be done to see if the reason for a large log is lack of log backups, no just take a log backup on the off chance that's the problem.
If shrinking the log file, shrink to a sensible size for regular database operations, not to 0 (unless you're planning on regrowing immediately). If as log is shrunk to nothing, the log will grow as soon as anything in the DB happens, and log grows are expensive, they have to be zero-initialised.
AS Jason told Shrinking frags thats true but you can do ddefrag after releasing the space.
And your rebuild will just grow the file again. Massive amount of resources for little to no gain. 150MB is nothing. The whole DB is tiny. If there's space issues on the drive, then what else is on the drive. Even usb flash drives are larger than 4GB these days. Trying to shrink this tiny DB is unlikely to give any useful returns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply