January 14, 2011 at 3:08 am
I have sql server 2005 on one of my server . The database size has exceeded so i had deleted some data from the table still the .mdf and .ldf files size remain unchanged . i deleted nearly 18000 records.
can any one help me....have heard of shrank database but tht would result in lowering the performance.
so can anyone pls guide me ...thanking u in advance
January 14, 2011 at 4:12 am
You said the size exceeded, but exceeded what?
The simple answer is do a full backup and follow up with a database and log shrink.
HOWEVER it is reallly concerning that you are deleting thousands of records cause the database size grows. Databases grow, thats a fact, they hold data and as they get more they grow.
Why did you delete? What did you delete? How did you choose what to delete?
Either i have misunderstood something or you are playing with fire!
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 14, 2011 at 4:36 am
Hello,
maybe you mean that you prefer not to have big datafiles. You can set a max file size, and create a new one when you need (when the first is almost full).
So you can keep all data, but into many little as you want files. Of course if you have enough space on server, or is this the matter?
Try to see if there are application logs that register too many things you're not interested in, this can cause a big increase of daily data.
January 14, 2011 at 4:36 am
i had taken relevant data to another server and than deleted those data from my server because the size in my server was less. but the size of mdf file and ldf file is same. Only Relevant data is kept on the server now.(i m not playing with fire thts wat i believe)..
so can u pls guide me..how too shrink the data now as i have deleted data but size has not reduce....
I have database logistics and a single table employees.
its mdf file is 8 gb and ldf file is 7 gb..
if u want any other information would be glad to share
January 14, 2011 at 4:44 am
the.hank (1/14/2011)
i had taken relevant data to another server and than deleted those data from my server because the size in my server was less. but the size of mdf file and ldf file is same. Only Relevant data is kept on the server now.(i m not playing with fire thts wat i believe)..so can u pls guide me..how too shrink the data now as i have deleted data but size has not reduce....
I have database logistics and a single table employees.
its mdf file is 8 gb and ldf file is 7 gb..
if u want any other information would be glad to share
USE [{db_name}]
GO
DBCC SHRINKDATABASE(N'{database_logical_name}', 5 )
GO
USE [{db_name}]
GO
DBCC SHRINKFILE (N'{log_logical_name}' , 0)
GO
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 14, 2011 at 7:26 am
Just so you know, deleting data from a SQL Server database doesn't reduce the file allocations for that database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2011 at 10:35 pm
I shrink my bith .mdf file and .ldf file but the .ldf file size increased tremendously from 7 gb to 20 gb. althoug the mdf file went from 16 gb to 12 gb..
can anyone help?.....
January 15, 2011 at 9:09 am
Is there an issue with space that you are trying to fix? At those sizes, I would just leave the databases at that size and let SQL Server use the space as more data is added.
Now that you have done this, you need to rebuild all of your indexes. This will cause your database to grow again because it needs free space in the data and log files to perform the index rebuilds.
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
January 17, 2011 at 12:39 am
the.hank (1/14/2011)
I shrink my bith .mdf file and .ldf file but the .ldf file size increased tremendously from 7 gb to 20 gb. althoug the mdf file went from 16 gb to 12 gb..can anyone help?.....
First of all the way you are playing with your database is very wierd.
The database file\files(.mdf,.ndf) grows when the data or index size within the database grows
which is very normal. Deletion of records is not the solution to reduce your database size.
If you want to reduce your database size you must first understand the allocated and unallocated space for database.
Even though shrinking is bad option but by seeing your previous replies . It seems like you are realy strugling for disk space.
So, you can follow the below steps may be to manage your database better.
1. Schedule the database full backup immediately if not schedules till now.
2. Trunate your log file (backup log dbname with truncate_only) not recommended but a suggestion for your situation only.
3. Shrink your log file (dbcc shrinkfile(2))
4. Take a full backup\differential backup now (manually).
5. Schedule log backup on a regular interval based on the load on the server.
But do remember don't ever try to shrink you database files because this will unnecessary create fragmentation and to remove that fragmentation will again increase extra load and will require resources of your server.
I hope it will help...
January 17, 2011 at 6:18 am
I looked back through the thread here, but I'm not sure I saw it. What is the recovery setting on the database? Is it set to Full Recovery? If so, do you have scheduled log backups running? If not, that would explain why your log file is constantly growing. The log must be backed up using BACKUP LOG. No, database backups do not backup the log. You must do that independently.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2011 at 6:25 am
I'm not even sure you have a size problem. From what you are saying the whole db takes less than 30 GB? It's very small by most accounts.
A 1 TB drive costs only a few 100$ so I don't see where the problem is in fixing the problem correctly by adding space or archiving correctly via jobs.
January 17, 2011 at 7:09 am
Ninja's_RGR'us (1/17/2011)
I'm not even sure you have a size problem. From what you are saying the whole db takes less than 30 GB? It's very small by most accounts.A 1 TB drive costs only a few 100$ so I don't see where the problem is in fixing the problem correctly by adding space or archiving correctly via jobs.
Actually, I just purchased a 2tb external drive for $94. So yeah, 30gb is practically on a thumb drive.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2011 at 7:20 am
Grant Fritchey (1/17/2011)
Ninja's_RGR'us (1/17/2011)
I'm not even sure you have a size problem. From what you are saying the whole db takes less than 30 GB? It's very small by most accounts.A 1 TB drive costs only a few 100$ so I don't see where the problem is in fixing the problem correctly by adding space or archiving correctly via jobs.
Actually, I just purchased a 2tb external drive for $94. So yeah, 30gb is practically on a thumb drive.
Agreed, but I wouldn't put a prod db on that! 😉
January 17, 2011 at 8:35 am
the.hank, this is what I typically have done in the past if I run into a customer with your situation:
1) Verify the free space in each file (code listed below)
2) If the Database is in full recovery mode, back up the log file first
3) Run DBCC SHRINKFILE(logfilename, sizeInMB) - Here I choose a size typically a few GB less than the current size of the file and keep reducing this and re-running it so it doesn't perform too much work or take too long to complete. In your case maybe do intervals of 1 GB. I've noticed that when you get close to the data size sometimes it takes a VERY long time to complete, but it's a good idea to leave some buffer room anyway.
4) Run DBCC SHRINKFILE(datafilename, sizeInMB) - Same process as step 3, but now you are doing it for the data file(s)
* If the log file does not shrink after backup, usually that means the active portion of the log has not wrapped to the beginning yet or you have pending replication transactions. In the case where the log hasn't wrapped you must wait or run dummy transactions through the DB so the log wraps but you can monitor it by running DBCC LOGINFO() (http://www.karaszi.com/SQLServer/info_dont_shrink.asp).
** When you delete records from data tables, always do them in small batches. This helps avoid contention and it also reduces the chances for issues with the log file filling up (especially when you are in SIMPLE recovery mode) because the small batches will have a chance to commit.
Code to verify free space by file (Reference: http://guide-line.com/archives/156)
SELECT
a.FILEID AS FileID
,convert(decimal(12,2),round(a.size/128.000,2)) AS [File_Size_MB]
,convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) AS [Space_Used_MB]
,convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) AS [Free_Space_MB]
,a.NAME AS LogicalFileName
,a.FILENAME AS PhysicalFileName
FROM
dbo.sysfiles a
Hope that helps!
January 17, 2011 at 1:29 pm
There may not be any available room in the database to shrink it. Please run the following script, it will tell you the amount of used/unused space in the database.
select [FileSizeMB] = convert(numeric(10,2)
, round(a.size/128.,2))
, [UsedSpaceMB] = convert(numeric(10,2)
, round(fileproperty( a.name,'SpaceUsed')/128.,2))
, [UnusedSpaceMB] = convert(numeric(10,2)
, round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))
, [DBFileName] = a.name
from sysfiles a
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply