DB Engine :: Can't Shrink MSDB Log File - Data Is 3GB But LDF Is 300 GB
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
ADVERTISEMENT
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
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
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
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
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
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
Oct 15, 2015
In one off my production box, we are notable to take a backups of MSDB . When i look at the error, it is failing locate allocation unit ID.. complete error as below
Msg 2533,Sev 16,State 1, Line 36 : Table error : Page (1:111720 ) allocated to object id 110623437, index ID 1, Partition ID 72057594043432960, alloc unit ID 72057594044874752 (type-inrow data) was not seen. This page is invalid or may have an incorrect alloc unit ID in its header.[SQLSTATE 42000]
Due this failure, we are unable to take the backup of MSDB database and our integrity check and reindex jobs also failured with the same.Also, I could see events of I/O issues with underlaying hard dirve with following name
DeviceHarddisk0DR0,has a bad block.
1. I dont no what could happen if restart my server, Question is: Does it recognize MSDB during server statup.
View 9 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
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
Sep 16, 2015
Suddenly one day I found some corruptions have occurred in my SQL Server 2012 installation. Because my 'msdb' has been marked SUSPECT/CORRUPT; hence no new work can be done as per my schedules.
I have gone about trying to repair it. I followed this article here: [URL] [the region 'Create new MSDB Database'].
I am facing some sizeable difficulties in doing this on my own. Steps 1. & 2. I have done. I am now stuck at step no. 3.My instance name is SQLEXPRESS. So, when I give,
SQLCMD -E -S<SQLEXPRESS> -dmaster -Q"EXEC sp_detach_db msdb" [as per the article], what I get is:
The System cannot find the file specified.
So I am stuck at that point. What's with the 'cannot find file specified'? The stored procedure, or the mdf, ldf files for my msdb? What is it?Also, if and when I am through with step no. 3 I would like to know about steps 5. & 6. also which are soon to follow [like how to do them correctly, safely and from where, the different options]. This is a huge priority for me to get my sql server up and running again because I can't do my other coding works without it. Everything is stalled. Slowly reaching the desperation, SOS mode..
View 7 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
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
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
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
Mar 27, 2000
I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution
Thanks
Ajay
View 1 Replies
View Related
Oct 20, 1999
On a database with a log file that has an unrestricted file growth, the file size exceeds 1 GB. Since this excessive was caused by a badly written update statement, I want to reduce the size to about 200MB.
After reading the BOL I was convinced that I only need to take two actions: truncate the log file (to create some free space in the log file)and shrink it.
These are the statements I executed:
backup log ODS with truncate_only
dbcc shrinkfile (ODS_Log, truncateonly)
After I executed these statements - BTW, there were no errors - the file size was still the same. Can somebody tell me why?
Thanks,
Stef
View 1 Replies
View Related
Aug 27, 1999
Hi,
I have created a new database in SQL Server 7 with the auto grow options set to on. I then added a whole load of new data to the table which made the transaction log file grow to 20Mb.
I then truncated the transaction log to remove all the completed transactions. The Enterprise Manager now shows the Log to only have 3MB of data in it but the file is still 20MB.
I have tried setting the truncate log on checkpoint option, and tried running DBCC SHRINK DATABASE and DBCC SHRINK FILE commands but these seem to have no affect on the file size.
Does anyone have any idea what I might have missed/done wrong?
Yours well and truly stuck,
Martin
View 2 Replies
View Related
Oct 25, 2004
I have Disk Xtender 2000 which was made by OTG Software , Legato and now EMC. I have an NT 4.0 PC with Microsoft SQL 2000. I have a drive space problem and need to shrink a 38 gig .ldf file called OTG03.ldf I also have a 2 gig .mdf file called OTG03.mdf How can I shrink this .ldf file. I'm not a DBA so being specific is greatly appreciated.
View 7 Replies
View Related
Dec 13, 2012
My DB's recover model is SIMPLE. Is it OK to schedule a SHRINK FILE only on the log files regularly? Any GOOD vs BAD about my plan? I want to do this because the log files keeps on increasing. Right now, the log file s on ENABLE AUTOGROWTH, FILE GROWTH = 10%, RESTICTED FILE GROWTH = 2,097,152.
View 8 Replies
View Related
Apr 7, 2008
I am geting growth alerts and need to shrink a log file that is 99% full, but it won't let me. Here is the message I get.
The transaction log for database 'SOM_System' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
What can I do in order to shrink this log file??
Thanks
View 2 Replies
View Related
Apr 30, 2008
I have noticed many times, even after log backup, shrink file (SSMS) is not reducing the file size. Even though there is free space shown in SSMS.
Subsequently I change the recovery type to SIMPLE and shrink – that does the trick for me.
Does anyone know why does this happen? Is there a better solution?
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Jun 4, 2007
Hello,
I have a database that the '.mdf' file is just huge. The physical size on disk is approximately 110 gig. I run weekly maintenance plans to rebuild the indexes on it.
I ran the 'sp_spaceused' command and got the following results:
database size: 106996.25 MB
unallocated space: 9614.35 MB
reserved: 99716504 KB
data: 13968056 KB
index_size 4002184 KB
unused: 81746264 KB
I was trying to clear the unused space, the numbers are telling me that I have 81 gig of space unused, but no matter what I do the '.mdf' file will not shrink.
I ran the following command: DBCC SHRINKDATABASE ({dbname}, 10,TRUNCATEONLY)
Any thoughts?
Thanks for the help
Richard
View 3 Replies
View Related
Sep 6, 2007
My database MDF file growth is high and due to space limitation i have to shrink the MDF file so how do i do that
View 5 Replies
View Related
Jul 20, 2005
Hello,Database log file on MSsql2000sp3 is 27gb when database itsself 305mb.I attempted to shrink the log file with Enterprise manager,but it wantsto use a minimum of 26.xxx MB,approximatley 27gb of disk space.when running the dbcc shrinkfile (file_name) message returned is "allvirtual logs are in use'Any ideas how to reduce the log file?Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
May 24, 2004
after backup the log with truncate_only,
dbcc shrinkdatabase('ABC', 10)
failed with following error:
Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.
I have put above statements with a job running every night, just got the same error on shrinkdatabase.
Is there a way to shrink the log without stop-restart the sql server?
-D
View 4 Replies
View Related
Jul 18, 2007
I have problem with my sql server developer edition.
I began reciving alerts regarding disk space, and found out that the msdb files are 5 GB!!! the data file is full, so I cannot shrink it, but the table usage shows only a few MB used by tables.
View 4 Replies
View Related
Mar 7, 2008
WHAT IS MSDB LOF FILE IS FULL?PLEASE HELP ME ON THIS ISSUE
View 1 Replies
View Related
Aug 15, 2007
When I log into Integrated Services on my SQL server, I see [Stored Package] -- File System and MSDB.
When I deploy/import my SSIS packages which should it go under? Is there a difference and if so what the difference?
thanks
View 5 Replies
View Related
Sep 6, 2001
I have a database (sql server 7.0) that has some big unused space in the transaction log. I tried to shrink the transaction log, but it seems it is not working. I used the same procedures to successfully shrink the log file for database of SQL Server 2000.
Here are what I tried:
shrink database from the Enterprise Manager.
or
dbcc shrinkfile (Spoper_Log, 40)
backup log Spoper with truncate_only
Does anybody know how to shrink the transaction log in SQL Server 7? This is a production database and I can't afford to lose any data.
View 4 Replies
View Related
Nov 18, 2004
Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.
I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...Thanks
View 1 Replies
View Related
Jun 23, 2008
hi
i have shrink log file of database use dbcc command
Message
Executed as user: sa. Cannot shrink log file 2 (abc.ldf) because all logical log files are in use. [SQLSTATE 01000] (Message 9008) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded.
View 2 Replies
View Related
