Recover space using SSMS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Recover space using SSMS

Author

Topic

carumuga
Posting Yak Master

174 Posts

Posted - 2010-03-08 : 02:34:01

Hi,

I tried to recover some space for one of the log file using SSMS (something equalivant to dbcc shrinkfile)RightClick db -> task -> ShrinkFile -> Choose appropriate log file. I could see "Available Free space" as 24% and keeping rest of the parameters to default. On completing the process, the free available space remains the same.

Could you please anyone explain why there is no reclaim of space even when there is sufficient space available to reclaim i.e. expecting the space to be released to the OS drives. I tried shrink all the log files mapped to that drive but no use in performing such actions.

Note: This is done when my drive is exhausted to 0 mb.

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2010-03-08 : 03:58:25

quote:
Originally posted by carumuga

Hi,

I tried to recover some space for one of the log file using SSMS (something equalivant to dbcc shrinkfile)RightClick db -> task -> ShrinkFile -> Choose appropriate log file. I could see "Available Free space" as 24% and keeping rest of the parameters to default. On completing the process, the free available space remains the same.

Could you please anyone explain why there is no reclaim of space even when there is sufficient space available to reclaim i.e. expecting the space to be released to the OS drives. I tried shrink all the log files mapped to that drive but no use in performing such actions.

Note: This is done when my drive is exhausted to 0 mb.

am not sure about ssms...
u can try this
dbcc sqlperf(logspace)
this will give u the amount of log space used..
you then need to truncate the log file to free up the log space.
u can then shrink the log file using shrinkfile command...

carumuga
Posting Yak Master

174 Posts

Posted - 2010-03-08 : 04:13:29

I aleady compared between these two utilities and both indicates the same value, dbcc sqlperf gives the Log size and the log space used (%). SSMS lists the log size and the remaining space (Logsize - Logspaceused). So, both seems to be one and the same. Thanks.

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2010-03-10 : 00:33:03

quote:
Originally posted by carumuga

I aleady compared between these two utilities and both indicates the same value, dbcc sqlperf gives the Log size and the log space used (%). SSMS lists the log size and the remaining space (Logsize - Logspaceused). So, both seems to be one and the same. Thanks.

did u try shrinking the log using dbcc shrinkfile

carumuga
Posting Yak Master

174 Posts

Posted - 2010-03-10 : 01:18:46

No, Is there any diff between DBCC SHRINKFILE and SSMS.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2010-03-10 : 12:21:43

Backup the log file and then shrink it.

carumuga
Posting Yak Master

174 Posts

Posted - 2010-03-10 : 22:38:32

backup log dbname with no_log on issuing this command, I'm getting the following error.

Backup and file manipulation operations (such as ALTER DATABASE ADD
FILE) on a database must be serialized. Reissue the statement after
the current backup or file manipulation operation is completed.

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2010-03-11 : 00:24:33

quote:
Originally posted by carumuga

backup log dbname with no_log on issuing this command, I'm getting the following error.

Backup and file manipulation operations (such as ALTER DATABASE ADD
FILE) on a database must be serialized. Reissue the statement after
the current backup or file manipulation operation is completed.

another backup or alter must be running on the server.
Execute this to find out the sessions.


select session_id, Text
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(sql_handle) txt

Execute one at a time.
1)take a full backup.
2)Truncate the log file.
3)Shrink the Log file.

carumuga
Posting Yak Master

174 Posts

Posted - 2010-03-11 : 01:45:10

I'm using SQL 2000, can u pls. pass on the query equivalent to the above one.

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2010-03-11 : 03:42:58

quote:
Originally posted by carumuga

I'm using SQL 2000, can u pls. pass on the query equivalent to the above one.

ssms is available from sql 2005 onwards and if your database compatibility level is 80, u can still use the query; just execute it in master database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources