May 19, 2010 at 1:34 am
hello every body,
i have a 29 gb database, showing 20 gb .mdf file and 7 gb .ldf file, and 1.8 gb .ndf file, when i am taking backup it has size only 15 gb tht mean it has 8 gb somethng unused space??
or somethng else??
and matter is not tht actually i wanna know if it is like dis n my log file growing every day around 1 gb so shud i shrink ldf and mdf and what wud be the performance issue?
May 19, 2010 at 1:43 am
What are the initial sizes set of the mdf and ldf on this database?
It could be that the DB is allocated at 29gb as predefined sizes, but the actual space used in these files
are not close to the initial sizes.
This would mean you still have a lot of space for it to fill, before a grow/autogrow will occur.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 19, 2010 at 2:16 am
initial size is 20 gb, bt recently i have deleted much data thts why is has 7 gb unused space i guess so shud i shrink data file?
May 19, 2010 at 3:21 am
If space is an issue, you could look into shrinking, but keep Fragmentation in mind......
Leave it at 20gb, at least you know there is ample space to grow within the file before additional space need to be allocated.
Personally, if 20gb is an issue, the database if on the wrong type and size of drive.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 19, 2010 at 9:07 am
No, you shouldn't shrink the database files unless you are absolutely sure you will never need that space. Eventually, you will need that space again and the data files will have to grow.
However, I would recommend that you get rid of the secondary data file. You can perrform a SHRINKFILE on that with the EMPTYFILE option. Once that is done, you can remove it. When you get to the state that you need more space, you can grow the mdf file instead of adding another file.
And finally, run this and post back the results:
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = {this database};
If the results of the above are LOG BACKUP, then you need to perform a transaction log backup. With a database in full recovery model, you need to be performing frequent (every hour, at least) transaction log backups. Read the article in my signature on managing transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2010 at 2:32 pm
Jeffrey Williams-493691 (5/19/2010)
<snip>However, I would recommend that you get rid of the secondary data file. You can perrform a SHRINKFILE on that with the EMPTYFILE option. Once that is done, you can remove it. When you get to the state that you need more space, you can grow the mdf file instead of adding another file.
<snip>
That might be the right answer. But, when I see stuff like that I wonder if might be a file that is not being used properly? Specifically, a file for Indexes to separate them from the data file. Just a thought.
May 19, 2010 at 5:06 pm
Lamprey13 (5/19/2010)
That might be the right answer. But, when I see stuff like that I wonder if might be a file that is not being used properly? Specifically, a file for Indexes to separate them from the data file. Just a thought.
True, but I find that in most cases that is not needed. However, if the system has been configured to use separate storage (LUNS) and the spindles are not shared, and there are separate IO channels for each LUN - then it can help performance. If not, then it just makes the system more complicated for no reason.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2010 at 2:05 am
before removing any files check their contents. Can you post the results of the following queries?
USE yourdb
select name from sys.filegroups
USE yourdb
select a.name as 'Index name', b.name as 'Filegroup',
c.name as 'Table Name' from sys.indexes a
inner join sys.filegroups b on a.data_space_id = b.data_space_id
inner join sys.objects c on a.object_id = c.object_id
where a.type = 1 and b.name <> 'PRIMARY'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply