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
ADVERTISEMENT
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
Mar 16, 2015
I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.
So, how do I get the free space out of the data file? - I've decided to:
1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables
There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?
View 8 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
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
Aug 13, 2014
I have a tempdb split into 4 files (5 if you include the log).
Autogrowth is disabled on the mdf/ndf files so that they can be used round robin (1 file per logical CPU).
Is there a way to be alerted when there is x% of free space left?
I know hwo to check the free space via t-sql but want to be able to be alerted. I could run a sql job that reports the free space and send a database mail message if under x% but wondered if there was a built in (or better) method?
I also have SQL Sentry.
SQL 2012 Standard
View 9 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
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
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
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
View Related
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
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
Jul 20, 2005
Hi,Received the following error during index creation of the tables. Thedata & log files are set to 'unrestricted growth' and enough spaceavailable on the disk. Any reasons?___________Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocatenew page for database 'Ultimareports'. There are no more pages availablein filegroup PRIMARY. Space can be created by dropping objects, addingadditional files, or allowing file growth___________ThanksJohn Jayaseelan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jul 21, 2015
I am currently SQL admin since our ordinary SQL folks are at summer vacation.
Yesterday SCOM alerted that File Group is running out of space on one not so critical database "The file group "PRIMARY" for the database "loganalys" in SQL instance "MSSQLSERVER" on computer "sqlserver2" is running out of space".
I logged in to the SQL server and checked the database in question. It is a very large DB with a size of 577 GB.The storage on which the database files resides has 123 GB free space so that isn't the issue.
The database is set to autogrowth 1 MB at a time (unrestricted) so that doesn't seem to be the issue either.However, in the database properties under General, it says Space avaliable: 570 MB.
I guess that this may be the issue, that this is under some treshold for SCOM to alert on.I have looked at the other databases and they have everything between 0,25MB to 270MB space avaliable (they are all set to autogrowth 1 MB, unrestricted).
I am not sure what this means and if I have to do something about it?
View 6 Replies
View Related
May 7, 2008
I have a requirement for moving all the non-clustered indexes to a new file group. Does anyone have a script for this?
Thanks.
------------------------
I think, therefore I am - Rene Descartes
View 1 Replies
View Related
Oct 17, 2007
Hi all
I have a table called ACTIVATION_CONSUMPTION which is in PRIMARY file group, in order to move this table to new file group [FG_ACTV], I have done the following
1. ALTER DATABASE [MYDB] ADD FILEGROUP [FG_ACTV] (i have not attached a file to this file group)
2. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACTIVATION_CONSUMPTION]') AND name = N'ACTIVATION_CONSUMPTION_PK')
ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] DROP CONSTRAINT [ACTIVATION_CONSUMPTION_PK]
GO
3.ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] ADD CONSTRAINT [ACTIVATION_CONSUMPTION_PK] PRIMARY KEY CLUSTERED
(
[ACTIVATION_CONSUMPTION_ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [FG_ACTV]
The above command executed successfully and now the table is in new file group [FG_ACTV]
My question is how come the table was moved to new file group though it has no files attached to it?
Thanks in advance.
View 5 Replies
View Related
Sep 1, 2015
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error:
The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB
templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
View 8 Replies
View Related
May 19, 2008
How to move a table to a new file group when the table DOES NOT HAVE A CLUSTERED INDEX.
I also don't want to , create a new table, move the data, drop the old table.
Is there a easier way?
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
Sep 1, 2015
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error:
The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB
templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
View 9 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
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
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
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
