MANUAL SHRINK DATABASE OR FILE

Apr 29, 2008



Hi guys

when we do manual Database shrink at normal office hours does it make any issues

or best way to control it

1. Does it lock database and runs the shrink statement.

2. at time of shrink users allowed or not.

3. transactions can be committed.

View 2 Replies


ADVERTISEMENT

Shrink Log File Database In Sql2005

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

How To Avoid Shrink File Or Database

Jul 20, 2015

I am having DB with 4 ndf and 1 Mdf, In properties

1.MDF- Intial Size 5GB and Autogrowth 5GB

2. 5 NDF- Intial Size 20GB and Autogrowth 5GB.

So we are using the template and creating DB's.S o we have more than 30DB on same size. But Sometime actual data was very low not even 15 GB.

is it good if i shrink the DB?

View 7 Replies View Related

DBCC Srhinkfile Didn't Shrink The File But Expand The Database Instead!!

Apr 6, 2001

I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.

I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.

Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.

View 4 Replies View Related

Shrink Log File In Specific Database In Mirror / Synchronized / Restoring State

Aug 12, 2015

I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).

View 4 Replies View Related

Manual Authentication From A Database

Apr 27, 2007

Assuming that I have a table [users] in a MSSQL 2005 database. the table has two columns. [userId] and [password].
 I have an aspx with C# page with two textboxes and a button.
I want to let the user login or send him an error message if the password or the username does not match from the table in database.
I always made this possible through the template database from administrating the website. but never tried to do it as simple as that. I'm lost here!
How can I do it?

View 3 Replies View Related

Shrink File Tool From EM Took All The Empty Space Allocated To Data File

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

Don't Permit Manual Access To Database

Jul 23, 2005

I've got a java application that connects to a sql server 2000database.The application must access with total permissions to database but Idon't want that anybody can insert or delete data with the corporativeadministrator of sql server 2000.How can I lock the corporative administrator in order to not permitmanual manipulation but my application can work properly?Thanks!

View 1 Replies View Related

Manual Back Of Database Error

Nov 27, 2006

HiI am attempting to take a manual backup of an SQL 2005 database, viaRight Click on MyDatabase --Tasks --Backup[Specify the filename path and type of backup]However, when I try to specify the filename or, in fact, do anything, Ireceive the following errorProperty BackupDirectory is not available for Settings'Microsoft.SqlServer.Management.Smo.Settings'. This property may notexist for this object, or may not be retrievable due to insufficientaccess rights. (Microsoft.SqlServer.Smo)The user I'm logged into the database with, however, has full rightswithin SQL.Does anyone have any ideas?Thanks!

View 1 Replies View Related

Manual Creation Of Aspnet Database Fails Does Not Allow Remote Connections

Mar 28, 2007

What do I have to do to get this to work? 
 
C:>aspnet_regsql.exe -A m -E
Start adding the following features:Membership
...............An error has occurred. Details of the exception:An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under thedefault settings SQL Server does not allow remote connections. (provider: NamedPipes Provider, error: 40 - Could not open a connection to SQL Server)
Unable to connect to SQL Server database.

View 3 Replies View Related

Mirroring :: Manual Failover Did Not Get Back Database On Secondary Server?

Jun 22, 2015

I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application
 
jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
 
During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover  to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.
 
After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_BMSSQLSERVER is not configured for database mirroring".
 
My Question is

Why manual failover did not get back database on secondary server upwhy the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring

 jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER

View 3 Replies View Related

Shrink File

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

How To Shrink A Log File?

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

My SQL 7 Log File Won&#39;t Shrink

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

How To Shrink 38 Gig .ldf File

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

Shrink Log File

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

Log File Can't Shrink

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

Log File Shrink

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

Trying To Shrink The '.mdf' File

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

How To Shrink MDF File

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

Shrink Log File

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

Manual Instal Of AdventureWorksToGo Database In Mobile Client Software Factory (mcsf)

Sep 12, 2007

I want to manually install the database for the reference implementation (I have SQL 2005 Express when I installed the Factory, but AdventureWorksMobileStaging database is not installed in my sqlExpress, so i try to install manually in that database ) I have noticed a number of scripts under the AdventureWorksToGoDBScripts folder. Is there any specific order of installing those scripts?Any other things that need to be followed for getting the reference database up and running?Thanks in advance
Varisai

View 3 Replies View Related

Cannot Shrink Log File 2 Because Files Are In Use.

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

Data File Will Not Shrink

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

Msdbdata.mdf File Is Over 5 GB, Cannot Shrink It

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

DB Engine :: Not Able To Shrink Log File

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

Shrink The Transaction Log File In SQL7

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

Log File Auto Shrink Question

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

Log File 80gigs &#043; Wont Shrink

Jul 25, 2007

i have a log file for a 200 meg database that is now over 80gigs. It is way out of control but DBCC shrink file just reports... the file is not part of a replication set.

Cannot shrink log file 2 (Database_log) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


I have read around and seen the suggestion to make a new database file and copy all the data to it. How is that done? I am using SQL 2005 Express with microsoft SQL server management Studio express.

View 5 Replies View Related

Trunc And Shrink Log (.ldf) File -&&> Trans Log

Sep 29, 2006

hi,

how can I trunc an shring a db transaction log?



thanks for reply....

View 3 Replies View Related

Cannot Shrink Log File 2 (ABC_Log) Because All Logical Log Files Are In Use....

Nov 6, 2003

A small database ABC with data only 5 mb but its log is growing everyday around 20 mb. I want to shrink its size like for other databases on daily bases.

1. backup log ABC with truncate_only
2. DBCC SHRINKDATABASE (ABC, 10)
got following error:
<<Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.>>

with no_log also tried but have the same error when dbcc shrinkdatabase..
any idea?

thanks
-D

View 8 Replies View Related

Log File Auto Shrink (Yes) Is Good Option?

Feb 22, 2012

Is Auto shrink is good option where database is very critical and there is no down time?

View 4 Replies View Related

Log File Shrink Not Releasing The Space On Disk

Oct 4, 2007

Hi all

My Transactional log size increased to 39GB, it is in full recovery mode,

To regain the space, i have done the following
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINK_FILE (LOG_FILE_NAME,500)
But not able to regain the space in the hard disk.

No Transactional backups to truncate the log file were setup.

Can you please tell me why the space was released and what should i do further to clean up the sapce

View 4 Replies View Related







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