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.
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?
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.
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
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?
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.
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.
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.
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.
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?
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.
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 .
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.
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.
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.
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
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.
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?
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?
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....
