SQL 2K Unallocated Space Won't Free Up - HELP!
Nov 5, 2004
I've gone through the forum and have seen several others with a similar situation. I recently noticed my .mdf file grew to 200GB yet the data in the file is only about 40GB. I run sp_spaceused and get the following: database_size = 176GB, unallocated space = 134GB, reserved = 43GB, data = 17GB, index_size = 19GB, unused = 6GB.
I've tried "dbcc shrinkdatabase (mydb, truncateonly)", tried restarting server, tried running a maintenance plan including a reindex of the database, and I don't know what to do next.
Does anyone know how to free up this space?
View 2 Replies
ADVERTISEMENT
Aug 17, 2006
HI,
I am short of space on one of our sqlservers and I want to claim unallocated space for one of my databases and give it back to OS.
I guess it is relatively simple task to do but unfortunately I am unable to find such a command. Can somebody help me and provide me that command.
Rgds
Wilson
View 1 Replies
View Related
Jul 20, 2005
At my current workplace, whenever I check table sizes using the'reserved' column from sysindexes, or sp_spaceused, I get a total forall user tables which exceeds the physical size of the database.Running sp_spaceused with no parameteres, I get a NEGATIVE value forunallocated space in the database, but only see this if I return theresults in GRID format in QA (text format gives less output):DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB-4874.80 MBReserved Data index_size unused16602800 KB7013752 KB2381904 KB7207144 KBIf I re-run sp_spaceused with the @updateusage='TRUE' option, thisoutput gets corrected to:DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB2773.76 MBReserved Data index_size unused8770680 KB6928168 KB1808096 KB34416 KBwhich shows a substantial difference in the Reserved/Data/Index/Unusedsizes.This happens every day - any ideas about:1) Why this might be happening on such a large scale, and2) Is it conceivable that these discrepancies in space allocationinformation could be causing performance problems? I can imagine thatif the database is trying to locate free pages on extents yet itsinternal view of these doesn't match reality then this could impededata insertion.Offers anybody?
View 2 Replies
View Related
Sep 26, 2007
Hi all,
When I am rebuilding the indexes on the tables, I am getting lot of free space( unallocated) on the database.
Before rebuilding the indexes , the size of the database = 385 Gb
After rebuilding the indexes, the size jumps to = 572 Gb (i.e.) This means 187 Gb of unallocated space .
The Command use to rebuild indexes is:
USE [databasename]
GO
ALTER INDEX [PK_index] ON [dbo].[tablename] REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )
GO
So, every time we rebuild indexes, we have to shrink the database
(or)
Is there anything else ,I should be doing.
Thanks.
View 4 Replies
View Related
Mar 1, 2005
Hi All,
When i run sp_spaceused on one of my database, PROD, it gives the following output
database_name: PROD
database_size: 4268.00 MB
unallocated space: -789.82 MB
reserved: 4654920 KB
data: 2929008 KB
index_size: 327272 KB
unused: 1398640 KB
Why is that the unallocated space is negative ?
On the drive where the datafiles resides there is enough free space . Also, the datafile and transaction are set to auto grow with unlimited file growth
FYI - the OS is Windows 2000 and the DB is SQL Server 2000 with SP3
Appreciate your time and help on this.
View 1 Replies
View Related
Apr 1, 2015
I am trying to track unallocated space (because on my database growth is not kicking in) So using below query but not working!!! Is there any way I can track 'database size' and 'unallocated space' on a single database?
create table A(
Rundate DATETIME NOT NULL DEFAULT(GETDATE())
,DatabaseName varchar(100)
,Database_size varchar (100)
,unallocatedSpace varchar (100)
,reserved varchar(100)
[code].....
exec AdventureWork..sp_spaceused ------not working------
View 2 Replies
View Related
Apr 22, 2008
Morning forum,
I'm having a problem to which I'm sure the answer is simple...
All I want is a list of databases on my server with their allocated size and the free space within. Something similar to the first table that sp_spaceused gives you but on a server wide scale.
As I say, I'm sure there's a simple solution out there, but alas Google has failed me.
Thanks in advance,
Dan.
View 4 Replies
View Related
May 7, 2008
xp_fixeddrives gives the free space on the server drives. Is there a xp which gives the actual size of the drive?
I need to calculate the %space used!
------------------------
I think, therefore I am - Rene Descartes
View 3 Replies
View Related
Jul 26, 2002
How do I find the available free space in the database, in the Database and the Log Segment
View 1 Replies
View Related
Jun 2, 2003
Hi, I have linked to 50 servers and use dynamic query (exec @sqlString8000)...
to get info about 1000 databases.
Now I wand to use xp_cmdshell to check each server C: fress space, how could I return the last 2nd line value to a global ##temp table?
<<5 Dir(s) 2,169,880,576 bytes free>>
<<NULL>>
thanks
-D
View 4 Replies
View Related
Dec 21, 2006
Hi All,
I was wondering if anybody has the script to view the free disc space. Currently I am using xp_fixeddrives stored procedure, but it is undocumented and might not be supported in SQL Server 2005.
Thanks.
View 4 Replies
View Related
Apr 25, 2001
I would like to know how to determine how big log and data space is, and how much of this space is free. I would like to create a script to warn me when less than 20% free space is left in log as well as data.
Ruud
View 1 Replies
View Related
Oct 10, 2005
Hi:
How can i get the Total and Free space of my filegroups, do you have some script for that?
Thnaks :eek:
View 4 Replies
View Related
Sep 19, 2005
Hi,
While I am importing few data to my SQL Server (2000) database, the free space is gettig increased in GBs. The database's File Growth is 1 MB (same for both Data Files and Transaction Files). The same database I restored on another PC and did the same process, it is working fine as there is no enormous growth in Free Space.
Any idea why this is
Regards,
Eldho
View 8 Replies
View Related
Oct 17, 2005
I was just reading a doc on SQL , that said that to free up the unused space from the DB , you should use the shrinkdatabase DBCC commnad. ....
I want to knw how to determine how much free unused space is there in a DB
Thanks,
View 2 Replies
View Related
Apr 19, 2007
How to check the free space in a DB file using TSQL?
------------------------
I think, therefore I am - Rene Descartes
View 20 Replies
View Related
Jul 3, 2015
My disk drive was full. So I deleted some records. Then I tried to shrink and it indicated available free space of around 5GB as indicated in the below image.
Once I ran the shrink for data file, though it completed successfully I didn't get the 5GB space as indicated. For log file shrink it shows available space of 200MB and when I run a log file shrink it does free up 200MB as indicated. So why is it not working with data file shrink? What am I doing wrong?
View 8 Replies
View Related
Mar 5, 2002
Hi,
I have some space available in the database, I tried dbcc shrink database and srrink file. I am not getting the disk space. But the amount of free space on the database sometime get increased.
Any help please
Thanks
John Jayaseelan
View 1 Replies
View Related
Apr 11, 2002
Hi friends ,
I have a database with around 2 GB space for the data and 5.8 GB for the
transaction log .
Now the problem is i do not have any more space on the system and data files requires more space than 2 G.B to execute some stored procedures .
Is it possible to decrease some space of the transaction log , say from 5.8 GB to 2 GB and allocate it to the data files .
My data and log files are on different drives . I did not find anything related to this topic in the BOL .
Can somebody help me with this problem ? Anthing related to this issue will be of great help to me since i have no expertise in this field .
Thanks and Regards
Christine S.
View 1 Replies
View Related
Jun 15, 2003
Hi,
I have to rebuild a clustered index.I want to know how to find out the free space in a database ?
I know that DBCC SQLPERF ( LOGSPACE )
will give free space in a log space.
Thanks,
copernicus.
View 2 Replies
View Related
Apr 6, 2005
hi,
I'm trying to calculate how much unused space i have on one datafile. My main goal is to determine the max space i can save by doing a dbcc shrink.
Any help is greatly appreciated.
ThanKs in advance.
Al
View 1 Replies
View Related
Jul 12, 2012
I inherited a SQL server (2005) that sits on a Windows 2003 box (upgrading to 2008 R2 and Win2008, ASAP) and on our SQL Data Drive (260GB - 22GB free) we have 16 databases (that are associated with our primary application). One database in particular is causing me quite a bit of pain - on Tuesdays. This table will consume all available free space (save 700kb) and place it within it's 'reserved' space. It's not being used and I can reclaim it by shrinking. I have done this for 3 consecutive weeks now and I have always thought that shrinking is not really a best practice.
Based on a job I scheduled to spit out various database stats I discovered the following:
Somewhere after 11pm on Monday (and on or before 11:30pm) the reserved space goes from 9GB down to 23 then 37MB on this particular database.
After 1:30am on Tuesday (and on or before 2:00am) the database consumes nearly all available disk space and thus expands its reserved space to 28GB
I believe it has something to do with the Indexes being rebuilt - per the Maintenance Plan the Index Rebuild is supposed to occur every Monday @ 11pm. On 6/18 it ran from 11pm - 11:44pm with no problems. Starting on 6/26 (and subsequently 7/2 and 7/9) they have started at 11 and ended anywhere between 2 - to 3 hours later. And with the following error:
Failed-1073548784) Executing the query "ALTER INDEX [PK_activityLog] ON [dbo].[activityLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Could not allocate a new page for database 'FA_PROD_SDDS' because of insufficient disk space in filegroup 'PRIMARY'.
Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The database 'FA_PROD_SDSS' is the one that I am having issues with.
View 2 Replies
View Related
Jun 30, 2015
what is the best way to make free space in tempdb?
View 5 Replies
View Related
Apr 26, 2001
hello everyone,
I have a question about the under what circumstances will a large database with mostly free space not shrink?
thanks
View 2 Replies
View Related
May 23, 2000
Hi,
In sql 7.0 , i would like to create a database with the size of 10Gb, in my server couple of databases already exist.
How do i know how much free space is there in File group.
we are having only one file group i.e PRIMARY.
Could anyone pls tell me about this.
Thank u.
--kavira
View 2 Replies
View Related
Nov 29, 2000
does anyone know if tempdb can be physically moved to a different partition on a disk drive on SQL Server 7.0? Since it can't be backed up I'm hesitant to use the sp_detach/sp_attach procedure because I don't want to crash it. If nothing else is available, I can attempt moving it this way at the end of the day and then just reboot to get tempdb back up again if the server fails, but I'd really appreciate a suggestion from someone who has more know-how than I do about system table operations. Thanks again
View 1 Replies
View Related
May 6, 2004
I've faced with this problem in my DB:
There are some tables that suffer inserts and deletes daily. These tables have 3 nonclustered indexes including the pk.
I perceived that the space used for these tables are growing day after day even ocurring daily deletes.
These inserts and deletes follow the keys of the pk in ascent order.
I ran DBCC SHOWCONTIG on the tables and got results like this:
- Avg. Bytes Free per Page.....................: 7996.3
When I transform the pk to clustered this problem doesn't happen.
You realized the consequence of this: the users complains because the DB is without space, but it's not true!
Anybody could help me to understand why the extents are not being deallocated?
Thank's for help!
View 8 Replies
View Related
Jul 14, 2004
Hi all,
I have to find free disc space in a drive from SQL Query and i am using MSSQL200. Does any body know the command for the same.
Also if i want to find hard disk space of a different machine on network, it is possible to get the data??
Also can i get the CPU/Memory usage data of our machine by some SQL/C++ commands??
Please let me know if u know any of the answer...
thanks
Alok
View 1 Replies
View Related
Feb 13, 2006
I saw something strange this morning that I can not work out in my head.
Customer contacted us saying that the login timeout for one of our web apps was timing out.
The login does a simple check of user name and password against a table and inserts the event into a tracking table. I traced the login routine and the query took zero seconds in the QA on the db server. Everything looked fine in the Task Manager and the Performance monitor except that the disk que spiked out when the login page was used. I pinged between the web and database server and that was fine. The disk had plenty of free space for the log and the mdf files. The db had about of 15mb of unallocated space. Nothing seemed to work until I allocated another 100 MB to the db on their test site. Suddenly no more time out. I tested the live site again and it was still happening (same db and web server). Bumped up the db size on the live site and boom no more problem.
The problem is "fixed" for the moment but I can not seem to reason why so I can actually fix the problem in the long run. I do not know why a simple little password routine would care about the difference between 15mb of unallocated space and 115 mb of unallocated space.
View 3 Replies
View Related
Jul 11, 2014
Having just archived quite a bit of data from the main Production DB, I now have around 15% free, reclaimable space sat in the data file.
I'm reluctant to run DBCC SHRINKFILE as that apparently causes a lot of Index fragmentation which will cause issues for performance - how else can the space be allocated back to the OS?
View 4 Replies
View Related
Mar 9, 2015
wmic logicaldisk where "DeviceID='C:'" get FreeSpace /format:value
FreeSpace=231379468288
View 1 Replies
View Related
Sep 24, 2007
We've got an alert setup on our production database to warn us when the log file(s) exceeds 7 gigs. The Alert is triggering:
"The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 7057656.00)."
However, according to the file system, the database properties screen files, and properties tabs, the log files combined are at under 5 gigs so this alert should not be going off.
The scary part is, when going to right-click on the database, choosing "tasks" and going to "Shrink > Files", the "Free Space" shows negative numbers for the first log file:
Log 1
Currently Allocated space: 118.69 MB
Available Free Space: -5323.24 MB (-4485%)
Log 2
Currently Allocated space: 4853.13 MB
Available Free Space: 411.20 MB (7%)
Has anybody ever run into this? Should I be worried that there is a bigger issue at hand?
Thanks!
View 20 Replies
View Related
Aug 24, 2007
Hello guys.
I want to konw how many rows a data page could contains.
So i do some test to prove it.
The follow sql scripts i used:
create table table1
(
col1 char(2)
)
go
declare @a int
set @a=0
while @a<700
begin
insert into table1 values('aa')
set @a=@a+1
end
I add 700 recrods to table1,then use dbcc page command oversee the data page.
The result indicate that:the whole recrods are all in one data page,and the "m_freeCnt" flag equal 396.
(8192-96-700*9-700*2=396)So i think i can add more records to the data page.To my surprise, after i add a new record, sql server 2005 allocates a new data page and assign the recrod to the new space....
any suggestion?
View 4 Replies
View Related
