August 18, 2008 at 3:43 am
Hi All,
I have gone through few topics regarding Database Shrink. But when I applied Shrink Command to my Database I am unable to see the result.
Let say, I have a Database "TestDB" and It is having 3 files like this
TestDB.mdf --> 270656 KB
TestDB_1.mdf --> 2123392 KB
TestDB_Log.ldf --> 1024000 KB
I applied the command "DBCC SHRINKDATABASE(TestDB,10)" , After applying this Command When I applied "SP_HELPDB TestDB", Its showing the same size.
1st of all I am not sure about this command. I mean to say what is the approach of this command to shrink the Database.
"DBCC SHRINKDATABASE(TestDB,10)"
"DBCC SHRINKFILE(TestDB,10)"
Please Explain these 2 command 1st, then I would like to know the Parameter is taking by this Commands.
My Question is here, Is Shrink reduce the size of file or not?
"DBCC SHRINKFILE(TestDB_log,10)" when I applied this command then its showing an error
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'TestDB_log' in sysfiles.
Can anyone please guide me for DBA ??
Cheers!
Sandy.
--
August 18, 2008 at 4:45 am
The shrink can only reduce the file space if there is free space within the file. If there's no free space, the file can't be shrink. It's not compression or anything like that. All shrink does is return free space within the file to the OS.
Shrink isn't something you should often need or regularly do. See here, and read the articles linked at the bottom
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
DBCC SHRINKFILE(TestDB_log,10)" when I applied this command then its showing an error
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'TestDB_log' in sysfiles.
As the error message indicates, there's no file named TestDB_log in that database. Check sysfiles for the names of the files. It's looking for the logical name of the file, not the name of the file on disk.
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
August 18, 2008 at 5:03 am
Gail,
Can you please make it little more clear..
Cheers!
Sandy.
--
August 18, 2008 at 6:01 am
As the error message indicates, there's no file named TestDB_log in that database. Check sysfiles for the names of the files. It's looking for the logical name of the file, not the name of the file on disk.
Gail, I have checked, the log file is there..
Can you tell me why again this error?
Cheers!
Sandy.
--
August 18, 2008 at 7:38 am
Did you check the names of the files in sysfiles? Or in the database properties?
August 18, 2008 at 10:36 am
Sandy (8/18/2008)
Gail,Can you please make it little more clear..
Can I make what more clear?
Gail, I have checked, the log file is there..
Can you tell me why again this error?
100% sure? Have you checked that the logical name of the file matches? SQL doesn't care what the file's called on disk. It depends on the logical name, which will be listed in sysfiles.
Are you in the right database?
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
August 18, 2008 at 10:59 am
Please post output of the following command:-
Use yourdbname
select name from sysfiles where filename like '%.ldf%'
MJ
August 19, 2008 at 1:33 am
Yes, I got it...
But I need know how its work..Is it reducing the Log File Size or doing some internal removing space from the page level.
Cheers!
Sandy.
--
August 19, 2008 at 1:43 am
But I need know how its work..Is it reducing the Log File Size
It reduces the size of the log file by releasing unused space within the file back to the file system.
or doing some internal removing space from the page level.
Don't understand. Logs don't have pages. Data files have pages and in data files pages are a fixed size.
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
August 19, 2008 at 1:56 am
August 19, 2008 at 3:02 am
Gail,
Can you just guide me...Before shrinking what are the necessary steps I have to check, I mean to say I will be sure after shrink my database will be consistence as before..
Can you guide me the steps..??
Cheers!
Sandy.
--
August 19, 2008 at 4:48 am
Shrink won't damage the database.
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
August 19, 2008 at 4:56 am
Thanks,
Cheers!
Sandy.
--
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply