DB Engine :: 2008 R2 - Shrink Data File Size?
Nov 9, 2015
I have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space. Â This is because we have removed a large amount of old content from sharepoint. Â The log file is fine. Â I have 60GB left in my drive that host the database files. Â I would like to shrink the datafile to get disk space back. Â I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so). Â
When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping. Â So i have to kill the process. what i should do to reduce this data file.
why it keep using up all the free space in the drive when i try to shrink the data file?
View 10 Replies
ADVERTISEMENT
Sep 26, 2015
On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 300GB+. The data (.mdf) file is only 3GB.
Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..."
I've already deleted much of the historyThe MSDB database is in "Simple" recovery modeI've done a full backup (in case the log was "waiting" for a backup)
I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.
View 7 Replies
View Related
Nov 30, 2007
Greetings:
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
Regards,
Derek
View 1 Replies
View Related
Nov 24, 2015
I am trying to shrink the log file as it is eating my disk space. increased to 100GB..message after running the query"Cannot shrink log file 2 (mydatabase_log) because the logical log file located at the end of the file is in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
"
(1)use mydatabase alter database mydatabase set recovery simple with no_wait dbcc shrinkfile(mydatabase_log,1) database [mydatabase] set recovery full with no_wait
(2)USE mydatabase;
GO
Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE mydatabase
SET RECOVERY SIMPLE;
GO
Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (mydatabase_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE mydatabase
SET RECOVERY FULL;
GO
View 9 Replies
View Related
Sep 12, 2011
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file
View 9 Replies
View Related
Sep 22, 2015
What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy
ONÂ
PRIMARY
( NAME = Dummy_data,
  FILENAME = 'D:....DATADummy.mdf',
  SIZE = 250MB,
  FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
  FILENAME = 'D:....DATADummy_log.ldf',
  SIZE = 50MB,
  FILEGROWTH = 5MB ) ;
GO
View 3 Replies
View Related
Jul 20, 2005
I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
View 2 Replies
View Related
Apr 30, 2015
I have issue with my DATA file ( MDF) .. The usage is 99.87% for database DB1 . File size is 3 GB . How do I reduce it ?
I have tried to shrink it by changing the recovery from FULL to SIMPLE and set it back to FULL .
I notice the index Defragmentation is high ..
Can I change the Initial Size into 1 GB for example ?
View 9 Replies
View Related
Jan 7, 2008
First off, I'm normally not one to shrink the hell out of data files (for obvious reasons), but this is a special case. We are setting up a mini test environment in preparation for migrating one of our systems to SQL Server 2005 (among other things).
The problem I have is that the test SQL Server I have to use has only about 50GB of disk space that I can allocate to databases, when the databases that I need to fit in there sit at around 130GB total. I've got 13 total databases that I have to fit in there. There are several logging tables in each, and lots of binary data that's really not needed for our testing. The first thing I did on my testing copies was to change the recovery model to simple, which chopped the log file.
So I've been truncating the logging tables and have been stripping out the unneeded data. I then have been running a script that I wrote to reindex, then shrink the individual files, then the database as a whole. Probably overkill, but it has worked on all of the DBs with the exception of one.
This particular DB is approx 21GB in total size (it's already come down from about 55GB), but when you look at the free space, it's showing 75% free inside the mdf file. I don't really care much about performance at this point, I just need to get the file size down and can't figure out how.
Any ideas?
BTW - this is the script that I wrote:
declare @tablename varchar(255)
declare @logfilename nvarchar(200)
declare @datafilename nvarchar(200)
declare @dbname nvarchar(200)
declare @sql nvarchar(1000)
set @dbname = ltrim(rtrim(db_name()))
set @logfilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.ldf%')))
set @datafilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.mdf%')))
/* Reindex Tables */
declare tablecursor cursor for
select '[' + table_schema + '].[' + table_name + ']' from information_schema.tables
where table_type = 'base table'
open tablecursor
fetch next from tablecursor into @tablename
while @@fetch_status = 0
begin
print 'Reindexing ' + @tablename
dbcc dbreindex(@tablename,' ',90)
fetch next from tablecursor into @tablename
end
close tablecursor
deallocate tablecursor
/*Shrink the crap out of the DB*/
set @sql = 'BACKUP LOG [' + @dbname + '] WITH TRUNCATE_ONLY'
print @sql
exec sp_executesql @sql
set @sql = 'DBCC SHRINKFILE([' + @logfilename + '], 1)'
print @sql
exec sp_executesql @sql
set @sql = 'DBCC SHRINKFILE([' + @datafilename + '], 1)'
print @sql
exec sp_executesql @sql
set @sql = 'DBCC SHRINKDATABASE([' + @dbname + '], 1)'
print @sql
exec sp_executesql @sql
View 4 Replies
View Related
Mar 8, 2015
My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.
What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.
Is there any way to find how much the backup file will be generate before we run the backup job?
i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db
currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled
View 3 Replies
View Related
Oct 18, 2015
way to read data from database already stored as question marks .that because by mistake i insert the data "arabic" from  the VS as sqltext but the field in the database is nvarchar now i want to get the data back.
View 4 Replies
View Related
May 7, 2015
In SQL Server 2014, how big for the block size is better for performance? 64 KB? 4 KB?
For normal database files, best practise is 64 KB disk block size. Not sure if it is same for memory-optimized filegroup.
View 12 Replies
View Related
Feb 27, 2015
In my database there is a big table and format is something like this:
CREATE TABLE [dbo].[MyTable](
[aaa] [uniqueidentifier] NULL,
[bbb] [uniqueidentifier] NULL,
[ccc] [nvarchar](max) NULL,
[ddd] [nvarchar](100) NULL,.......etc.........
There are some more columns with more 'nvarchar' (max) and other INT data types. Anyway, I know a page is 8K size. How do I find out how much space does A ROW takes with above datatypes? If users add 5000 Rows per day, how do I figure out how much size the table will increase?
View 9 Replies
View Related
Jul 2, 2015
Can we do database shrink in volume not full to manage spaces. Only few gb spaces are available, so i want to shrink the database. How to do that.
Â
DB size-775 Gb
Free Dis size-156 Gb out of 931 GB
How to shrink database partially?
View 4 Replies
View Related
Oct 7, 2015
I've never worked with the XML data type in SQL Server, although I know its been there for a few iterations of SQL SErver. Now I've got a situation in which it might store some configuration data as XML, since that's the way it comes. (We had thought about storing the data in a VARCHAR(MAX) field.)
The first question is does the XML data type have a size limitation? For example do you do something like:
ConfigFile XML(1000) NULL
Or is it just something like this:
ConfigFile XML NULL
The second question is persisting the data to a file. As the name I choose for the variable suggests, we want to save the data from a configuration file into a SQL Server database. How do we go about doing that? We'll be developing a C# application, it will read and write the data both from the SQL table and the user's local HD.
View 5 Replies
View Related
Apr 21, 2015
Is it possible/advisable to change this setting with users connected? Â There are a number of web based users and an agent job running every 30 seconds.Â
USE [master]
GO
ALTER DATABASE [Bla] SET AUTO_SHRINK OFF WITH NO_WAIT
View 5 Replies
View Related
Jan 8, 2008
I need some advice for an issue with a production transaction log min size having been set to 50 GB by some rouge developer while I was on vacation. Now, as the only resident DBA, I'm responsible for going in and reducing it down so we can free up limited disk space. It’s in SQL 2000 and participates in replication.
Because the log file cannot be set below its current minimum size, I’m a little stumped. Can I somehow force the minimum size by updating the table database.dbo.sysfiles?
Otherwise, what do you-all think of this plan?
1.)Back up the database.
2.)Verify no open transactions with DBCC opentran(‘database’) and proceed accordingly.
3.)Change it to single user mode.
4.)Set it to Simply Recovery mode.
5.)Issue a checkpoint to clear the log.
6.)Script out push replication.
7.)Delete replication.
8.)Detach it.
9.)Delete the Transaction Log.
10.) Attach it and let SQL Server recreate a transaction log to the SLQ Server default size.
11.) Run the Replication Script.
12.) Start the Snapshot Agent.
13.) Restart the Transaction Log Reader Agent.
14.) Celebrate a Victory!
Thanks in advance for your help!
Jeffrey.Hoover@gmail.com
Minneapolis, MN
View 3 Replies
View Related
Jun 28, 2004
I want to shrink database:
1. backup log ABC with truncate_only
2. dbcc shrinkdatabase(ABC, 10)
Cannot shrink log file 2 (ABC_Log) because requested size (113792KB) is larger than the start of the last logical log file.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 14432 12800 14432 12800
there is no transaction in this ABC database. Is there way to shrink it?
Not quite sure about "because requested size (113792KB) is larger than the start of the last logical log file.". The current log size is 113 mb.
thanks
-D
View 2 Replies
View Related
Jun 17, 2008
Hi all;
i m using SQL 2000, i have a database with 86G mdf and 56G ldf size. i shrink the ldf and it reduced to 32M, however, i did not do anything on my mdf file, but the size of mdf has been reduced to 28G. just would like to check, is this correct?why is mdf size reduced when i only shrink my ldf? hope can help. thanks
View 2 Replies
View Related
Jul 23, 2015
Is it possible to manually force/call/start the system AUTOSHRINK process? I have an issue that appears only when the engine shrinking process is running and I need this to reproduce my bug.
I know how to start a "regular" database shrink process with:DBCC SHRINKDATABASE(xxxx);, but this is not the same as one started from the database engine.
View 13 Replies
View Related
Feb 11, 2002
I have a tempdb that was created at 1Gig. I don't know why but I want to shrink it below the original creation size. Is there a way to shrink this file or create a new file and delete the old?
I have tried shinkfile and shrink database with no luck.
Thanks Jeff
View 6 Replies
View Related
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
View 1 Replies
View Related
Oct 20, 2011
How do i check the size of the datacache allocated from the buffer pool by sql server?
DMV or anything to show me the pool allocation sizes for the various pools in sql server i think i may be able to work from there.
View 9 Replies
View Related
Feb 19, 2013
I have a database whose log file size is 4 time greater then data file size, and its continuously growing day by day. Recently face limited disk related issue.
Is there any way to truncate log file???
What is impact on db if i truncate log file???
Is there any way to prevent this file continuously growing???
View 13 Replies
View Related
Apr 27, 2008
Hi,
i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns
dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB.
i try to write this a got stuck.
select sysdb.database_id,sysdb.name,sysdb.compatibility_level,
sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster
where sysdb.database_id = sysmaster.database_id
can anyone help me with this script?
THX
View 13 Replies
View Related
Mar 16, 2015
one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
View 9 Replies
View Related
Jun 19, 2015
I wrote a custom procedure to shrink the db log files alone. It works well always but yesterday in one of the server the job execution was failed.
Error msg:
: The log for database 'ReportServer$DB' failed to grow while shrink in progress. Please retry. [SQLSTATE 42000] (Error 50000) Error 9020, Severity 16, State 2,
The code is v simple. It just shrinks the db log files of all user dbs.
I also checked and confirm you that against all dbs the auto-shrink feature is disabled.
View 1 Replies
View Related
May 19, 2015
In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. Â I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
I have also tried using the command:
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev#, FILENAME = '<new location');
Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.
I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.
I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.
View 2 Replies
View Related
Apr 28, 2015
I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
View 1 Replies
View Related
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
Nov 13, 2015
We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.
Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can I go about splitting the data from 1 MDF files into multiple data files? How about moving indexes ?
SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) -Â Enterprise Edition (64-bit)
View 10 Replies
View Related
Apr 27, 2004
SELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
SELECT cntr_value/1024 size_in_mb ,
(SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
) a
I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
The above query i am using is for logfile size. Can any one help me with datafile size plz.
I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
May be i am unaware of a simple stored-procedure that can do this for me.
Howdy!
View 3 Replies
View Related
Jul 9, 2015
I have an Excel file with .csv extension . it has on sheet with name Sheet1.
Now, I'm trying to insert this Excel data into one #temp table. I tried with syntax:
----------------
Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1;Â
[Code] ...
But, I'm getting error:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
If I'm executing for .xls file this statement is working finr and rows are inserted into #temp table. How to take excel file of .csv extension??
View 3 Replies
View Related
