DB Engine :: DB Autogrowth Turned On In Secondary Data File Still 0% Internal Free Space

Oct 30, 2015

Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below

name    fileid    filename    filegroup    size    maxsize    growth    usage
XX    1    I:DataMSSQL.1MSSQLDataNew XX.mdf    PRIMARY    68140032 KB    Unlimited    0 KB    data only
XX_log    2    I:DataMSSQL.1MSSQLDataNew XX_log.LDF    NULL    1050112 KB    2147483648 KB    102400 KB    log only
XX_2    3    I:DataMSSQL.1MSSQLDataNew XX_2.ndf    PRIMARY    15458304 KB    Unlimited    0 KB    data only
XX_3    4    I:DataMSSQL.1MSSQLDataNew XX_3.ndf    PRIMARY    13186048 KB    Unlimited    0 KB    data only
XX_4    5    I:DataMSSQL.1MSSQLDataNew XX_4.ndf    PRIMARY    19570688 KB    Unlimited    204800 KB    data only
XX_5    6    I:DataMSSQL.1MSSQLDataNew XX_5.ndf    PRIMARY    19591168 KB    Unlimited    204800 KB    data only

2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.

What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its  other files.

What more data i need to ensure i submit an analysis to this.

View 5 Replies


ADVERTISEMENT

DB Engine :: How To Change Location Of Secondary Data File

Nov 9, 2015

I added a secondary data file to TEMPdb yesterday and gave it a wrong location by mistake. If I try to change the location, then I am getting an error now. I think that is because TEMPdb is in use and that is why I cant change it's secondary file's location. Do I need to take TempDB offline and then change the secondary file's location??

View 3 Replies View Related

Free Space In DB File.

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

File Group Free Space--urgent

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

Log File Alert And Negative Free Space!?

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

Shrink DB File By Increment To Target Free Space

Mar 9, 2007

This script can be used to shrink a database file in small increments until it reaches a target free space size.

It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment until it reaches the target free space.

It is often better to shrink database files in small increments so that it can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and makes it easier to interrupt without losing all progress.



-- Shrink_DB_File.sql
/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.

Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
*/

declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

-- Set Name of Database file to shrink
set @DBFileName = 'MyDatabaseFileName'

-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000

-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 50

-- Show Size, Space Used, Unused Space, and Name of all database files
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

declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin

set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)

exec ( @sql )

print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

end

select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Show Size, Space Used, Unused Space, and Name of all database files
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







CODO ERGO SUM

View 5 Replies View Related

Procedure Of Function To Show Free Space To A File

Jun 25, 2006

Any idea of a function or procudure the returns the free space of a data file so I can see when I need to reclaim free space.

View 3 Replies View Related

SQL 2012 :: Free Space After Moving Tables From One File Group To Another

Aug 13, 2014

Recently maintenance was done removing some tables from the original filegroup in one drive of our SQL Server 2012 Standard Edition 64bits to another created on a separate physical drive. I was expecting the full amount of data moved to the secondary filegroup to show up as unused on the primary filegroup but that doesn't seem to be the case. Do I have to do anything after the move to release that space, not to disk, but to the database as unused?

View 2 Replies View Related

Bug: Data File Autogrowth Set To 12800%

May 11, 2006

SQL2K5 SP1the autogrowth setting in one of my database's primary filegroup datafile keeps having the value of 12800%, which was originally set to100MB, everytime the service is restarted. the same occurs whenever irestore a backup of this database in our development environment.WTF? this issue does not happen to other filegroups. only on theprimary data file. whenever this happens the 4GB data file grows tomore than 70GB (even the math is incorrect) with about 95% of UNUSEDspace.has anyone else come accross this BS or anyone knows how to preventthis from happening? is MS aware of this not-so-funny joke?thanks

View 2 Replies View Related

Why Data Page Save Some Free Space?

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

The Log File For Database 'Home_alone' Is Full. Back Up The Transaction Log For The Database To Free Up Some Log Space.

Feb 4, 2008



Hi all

Iam working in Prodcution ENV,Please help how make space


The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.

View 10 Replies View Related

TempDB Log File Running Out Of Free Space While Running DBCC CheckDB On Large Database

May 28, 2015

In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.

What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.

View 3 Replies View Related

DB Engine :: How To Release TempDB Space After Data / Batch Loads

Apr 22, 2015

I have scenario where I have process that loades data into SQL server 2012 database by doing some manipulation on data like sorting , aggregation, etc. Once this process is completed it's not free up the Tempdb space.  If I restart the database, then it does.

is there any way (apart from shirking) to release space for Tempdb, like writing some post SQL queries to delete/ truncate the data and logs from temp db?

View 8 Replies View Related

How To Remove Secondary Data File????

Jan 16, 2003

I have a SQL Server 2000 database with a primary data file (MDF) and a secondary data file (NDF). I would like to remove the secondary file and only have the MDF. Is this possible?

Thanks, Dave

View 3 Replies View Related

Can I Move To A Secondary Data File

Jun 22, 2007

I have a database that has been running well for a few years.
It has a single data file.
It has now become very large and is creaking and running slow sometimes.
Is it possible to now create a secondary data file or do i have other options?
Many Thanks

View 4 Replies View Related

Secondary Data File Extension Changes To Mdf After Restore

Apr 18, 2007

i have sql server 2000 db with two data file... primary data file has extension mdf and secondary file has extension ndf (as per microsoft recommendation)..



when i try to backup the db and restore thru the enterprise manager .. in the restore -> options window ... i see both the files has the same extension mdf.. and when the restore completed, the new database still has extension mdf for both the file..



why this behaviour?



* i even try to create a new test db with two files, still its the same behaviour.

View 10 Replies View Related

SQL Tools :: Adding Secondary Data File In AlwaysOn?

Jul 27, 2015

how to add the Secondary Data file to the Database that is a part of the Always on Availability on SQL Server 2012.

View 3 Replies View Related

Free Space

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

How To Find The Free Space

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

How To Return 'Free Space' Value?.....

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

Free Disc Space

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

How To Determine Space Used / Free

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

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 View Related

Free Space Of My Filegroup

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

Free Space Growth

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

Free UNUSED SPACE

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

Why Can't Get Available Free Space After Shrinking

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

Dbcc Shrinkfile Does Not Free Space To OS

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

Free Some Space Of The Transaction Log .. Urgent Help Please .!!

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

How To Find Out Free Space In A Database ..?

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

Calc Free Space In Datafile

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

Database Consumes Available Free Space?

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

Best Way To Make Free Space In TempDB?

Jun 30, 2015

what is the best way to make free space in tempdb?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved