Shrinking LDF File - Query Running Indefinitely

Jul 7, 2004

My intention is to include a sql job to schedule for shrinking log files to half of its original log file size (if half of their original size is greater than 5MB)

The code is below. But while executing the code it is keeps on running indefinitely. can somebody throw some light what cause it to go into an indefinite loop.

Thanks
Babu

code:
====
DECLARE db_file_name CURSOR FOR
SELECT name , ceiling(size/128.0) * 0.5
FROM master..sysaltfiles
Where rtrim(lower(filename)) like '%.ldf'

DECLARE @f_name varchar(128), @new_f_size int

OPEN db_file_name
FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
WHILE @@FETCH_STATUS = 0
BEGIN
If ( @new_f_size > 5)
BEGIN
DBCC SHRINKFILE ( @f_name, @new_f_size )
FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
END
END
CLOSE db_file_name
deallocate db_file_name

View 6 Replies


ADVERTISEMENT

JDBC Query Running Indefinitely (Connection Missing)

Apr 3, 2007

We see an unusual case where a query seems to be taking a long time (more than 30 minutes) as shown by the Java thread dump (below) - however the SQL server DB does'nt show any corresponding Connection for the query at the lower layer.



The JDBC layer seems to be "in progress" as far as processing the results of the query are concerned. If the Connection was dropped or had a failure we should have seen a corresponding SQLException in the JDBC layer - which is also not the case.



Any tips on how to debug this? Is there a timeout set on the JDBC Connection which causes it to wait before it detects any failures?



thanks





"JMS Session Delivery Thread" daemon prio=6 tid=0x0000000006434780 nid=0x868 run
nable [0x0000000020d6e000..0x0000000020d6f860]
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep
aredStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedState
mentExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source
)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unkno
wn Source)
- locked <0x00000000955574e0> (a com.microsoft.sqlserver.jdbc.TDSWriter)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate
(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del
egatingPreparedStatement.java:101)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del
egatingPreparedStatement.java:101)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAc
cessImpl.java:213)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistenc
eBrokerImpl.java:2021)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro
kerImpl.java:977)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro
kerImpl.java:1014)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro
kerImpl.java:884)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeAndLinkOneToOne
(PersistenceBrokerImpl.java:1074)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeReferences(Pers
istenceBrokerImpl.java:1050)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistenc
eBrokerImpl.java:1981)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro
kerImpl.java:977)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro
kerImpl.java:1014)
at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro
kerImpl.java:884)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati
ngPersistenceBroker.java:220)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati
ngPersistenceBroker.java:220)
at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati
ngPersistenceBroker.java:220)
at com.serus.db.ojbutils.SerusPersistenceBrokerHandle.store(SerusPersist
enceBrokerHandle.java:178)
at com.serus.common.OJBDAOUtil.store(OJBDAOUtil.java:636)
at com.serus.dao.wip.AbsWipDAO.saveDTO(AbsWipDAO.java:251)
at com.serus.dao.wip.tx.LotTXDAO.saveLotTx(LotTXDAO.java:56)
at com.serus.manager.wip.txprocessing.AbstractTxProcessorImp.storeLotTx(
AbstractTxProcessorImp.java:1380)
at com.serus.manager.wip.txprocessing.LotTxProcessingMoveStage.storeLotT
x(LotTxProcessingMoveStage.java:802)
at com.serus.manager.wip.tx.LotTxManager.saveLotTx(LotTxManager.java:248
)
at com.serus.manager.wip.tx.LotTxXMLProcessor.parseMessageOriginal(LotTx
XMLProcessor.java:137)
at com.serus.manager.wip.tx.LotTxXMLProcessor.parseMessage(LotTxXMLProce
ssor.java:78)
at com.serus.events.listener.SerusJMSListener.onSerusMessage(SerusJMSLis
tener.java:197)
at com.serus.events.listener.AbstractEventListener.onMessage(AbstractEve
ntListener.java:53)
at progress.message.jimpl.Session.deliver(Unknown Source)
at progress.message.jimpl.Session.run(Unknown Source)
at progress.message.jimpl.Session$SessionThread.run(Unknown Source)

View 1 Replies View Related

Running SQL Query From Batch File- MS SQl 2000

Mar 25, 2008

Hello,
I am looking for some way to run a sql query from a batch file on the SQL server. The query needs a user entered parameter, if that can be accomodated it would be great.
I read about some osql command & tried to run it but it kept saying "Server does not exist or access denied."
Please give me the steps to go about this.
Thanks,
-R

View 4 Replies View Related

Shrinking The Log File

Apr 25, 2002

I have a 13 Gig Log File with only 121 Mgs of space used. I have run the DBCCSHRINKFILE command and it has shrunk it by about 100 Mgs. Why can't I get it to shrink to a reasonable size.

View 2 Replies View Related

Shrinking Db File

Sep 18, 2001

Have a 6G data file in a single filegroup.

Using dbcc shrinkfile to shrink.

Does not have any effect.

Any ideas?

View 1 Replies View Related

Shrinking The Log File

Sep 15, 2000

Hi, is there any other way to shrink the log file other than truncating every time it gets bigger... I have a 800MGs database and the log is getting as big as 11Gigs... any suggestions other than truncating it? Is there a way to set it off?

View 1 Replies View Related

Shrinking Log File

Nov 29, 2000

I'm trying to shrink the log file. I have used the suggestions that I have seen posted previously. This is the script I've been using.

DECLARE @DB VARCHAR(40)

SELECT @DB = 'PBDSSTEST'

CHECKPOINT

EXEC('DBCC SHRINKFILE ('+@DB+'_log, 1, NOTRUNCATE)')

EXEC('DBCC SHRINKFILE ('+@DB+'_log, 1, TRUNCATEONLY)')

CREATE TABLE t2 (char1 char(4000))

DECLARE @i int SELECT @i = 0 WHILE (@i < 300) begin INSERT t2 values ('a') SELECT @i = @i +1 END DROP TABLE t2

BACKUP LOG PBDSSTEST TO PBDSSTESTLOGBACKUP

Before I ran this the used portion of the log was 1795.12 MB, the free was 1570.50 MB for a total of 3365.62 MB.

After completing this the used portion was 25.62 MB, the free space was 3263.50 for a total of 3289.12 MB. It only srhunk about 26 MB. Why won't it shrink it anymore. There was plenty of data in the used at the time of the shrinkfile command so I would have thought it would have shrunk it a lot more.
Please help. I don't understand why it won't shrink it anymore than it did.

View 4 Replies View Related

Shrinking Log File

Oct 12, 2004

When I try to shrink the log file for my databases I am getting following message.

Cannot shrink log file 2 (cm_archive_mp_Log) because all logical log files are in use.
what should I do to successfully shrink the log file.
Thanks.

View 1 Replies View Related

Log File Shrinking

Nov 8, 2006

how can we shrink the log file to the required size without affecting the live database .....

Thanks in advance

View 2 Replies View Related

Shrinking Log File

Aug 29, 2007


I have a problem with shrinking a log file on SQL 2000. The log file has grown to 25gb (the db is 30gb) . The DB was in simple recover mode but I have changed it to full so that the log file can be backed up. I have run the dbcc shrinkfile on the log file several times (plus log file backups) and it has increased the logfile size. Each time I have run shrinkfile I get €˜Cannot shrink log file 2 (db_Log) because all logical log files are in use€™ message. I have used the dbcc loginfo command and this shows that all the virtual log files are in use. The db also publishes 4 snapshot replications. Any suggestion for how I can easily shrink the log file?


thanks

Simon

View 5 Replies View Related

Shrinking 2000 Log File

Aug 13, 2001

I have a database that was loaded with large amounts of data before today. I backup up the db and trans log. presently the DB file is 328 while the trans log show 428 meg with only 28 meg being used. If I try to shrink either the database or the log files, the log file wil not shrink.. It tells me to set it to a minimum of 28 meg but still not shirnk. I realize this was a problem in 7, any quick fixes, in a hurry. Running SQL 2000 SP1

View 1 Replies View Related

Automatic Log File Shrinking

Oct 16, 2007

I'd like to create a stored procedure to automatically shrink the transaction log file on a certain database monthly. As I am not the DB admin (it's a web-based db on a remote server), I'd like to put together a script to create the procedure so I can just send it to the DB admin to run. It seems like it should be pretty easy, but I'm still pretty new to SQL server. Is anyone able to give me some pointers?

View 1 Replies View Related

Shrinking A Log File Every Night

Dec 13, 2007



I have a relatively high usage 2005 database whose transaction log grows every day.

Untill now I have been manually shrinking the log files .. but I would like to automate this process.

In the maintenance schedule that runs every night

1 Do a full backup to disk
2. Do a transaction log backup to disk
3. run the following TSQL

USE [MyDB];
GO
ALTER DATABASE MyDB
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (MyDB_log, 3000)
GO
ALTER DATABASE MyDB
SET RECOVERY FULL;
GO

4. Backup disk files to tape.

however it doesn't work !! the TLOG file does not shrink, even though its the same as the manual job that I run?

There is nothing in the SQL logs .. but in the event log I get

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12291
Date: 13/12/2007
Time: 00:50:10
User: NT AUTHORITYSYSTEM
Computer: ALGSQL1
Description:
Package "MyDB_FULL_BACKUP" failed.
For more information, see Help and Support Center

any ideas ?

View 8 Replies View Related

Difference Between Shrinking A DB And A FILE

Jun 14, 2006

Hi,

What is the difference between shrinking a DB with that of a file.

When you select a DB to shrink will it not shrink all the files in it.

Because when I select Files to shrink it does not prompt me with
a specific file.

So I am confused as to why SQL server 2K5 asks if you need to shrink DB or FILE

Tnx

View 1 Replies View Related

Shrinking The Log File On A DB That Is Mirrored

Nov 17, 2006



Hello,

I'm having couple of DB that are mirrored.

my concerne is regarding the Log file size.

I'm running the following steps:

BACKUP DATABASE [DBName] TO DISK = N'Backup_File' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

then

BACKUP LOG [DBName] TO DISK = N'Backup_File' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

if I try to run a SHRINKFILE (DBName_log, 20)

I receive the following info:

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

I'n a bit tense using a

BACKUP LOG [DBName] WITH TRUNCATE_ONLY

as it's part of a mirroring.

but I need to reduce the size of the log file.

thank four your suggestions

Eric

View 10 Replies View Related

Shrinking .ldf File And .mdf File In Sql Server

Jan 27, 2008



please advice me what does .ldf file consist of and can i shrink the .ldf , is it adviceble to shrink after the backup and how frequently it can be done on a production db



please advice me ,can i shrink the .mdf , it is adviceble to shrink after the backup and how frequently it can be done on a production db


Thanks

View 4 Replies View Related

Problem Shrinking Data File

Dec 3, 2007

(SQL 2005) I have a few databases that are currently hovering at around 150GB. (The following only concerns the .mdf) Of this total size, only about 90GB is real data. The other 60-70 is data from unecessary audit tables. Recently, we've been having some space issues on a 1.7TB server. We've decided that deleting these audit tables and reclaiming the 60-70GB per database is the route we're going to take.

What we did: DELETE <table> (per audit table). Now, it shows that the database size is ~90GB and there is the 60-70GB of unallocated space.

Then, we ran a DBCC SHRINKDATABASE (db_name) overnight. Checked space the next morning and only about 10GB had been reclaimed. We've tried a number of other alternatives of which, none have been successful.

(DBCC SHRINKFILE(file, space), drop and recreate tables, etc.)

Can anyone think of any reason why these commands would not be working? If I can provide any additional information, please let me know.

Thank you in advance for any input!

View 5 Replies View Related

Shrinking 27GB LDF File (solved)

Jan 8, 2007

Hi,

I have an interesting problem here. I am running SQL Server 2005 and have a large database. After running some scalability tests over the weekend my MDF file size went up to 25GB and LDF file size went up to 27GB filling up all available disk space. I didn't do any backups, so it is understandable why the LDF file size is so large.

Now I am trying to shrink the LDF file. However, DBCC LOGINFO indicated that all LVFs within the LDF are active (Status = 2). I detached the database to make sure no active connections exist and ran CHECKPOINT, but still DBCC LOGINFO lists all LVFs with status 2. Since all LVFs are up to date I can't shrink my LDF file.

Why are all LVFs still active after detach/attach and checkpoint?
How can I shrink the LDF file?

Thanks.

Alec

View 1 Replies View Related

Ex Post Shrinking File Sizes

Jul 20, 2005

Is there a fairly quick and dirty way to shrink the file size allowedfor a DB on SQL server after the DB is in use?Our old DBA allowed 3 gb for log files and we don't need nearly thatmuch. However, the space is still being used and reserved. Can Ishrink that "maximum" and clear up the space?I don't want to just copy all the tables and primary keys and open anew one with the new data, though that is plan A right now.I cant just change sysfiles, it yells at me.

View 1 Replies View Related

Shrinking Log File In Conjuction With Log Shipping

Apr 19, 2007

I've been searching and reading for best practices on how to maintain a reasonable log file size while engaging in Log Shipping. I have found plenty of information on these subjects on their own, but not on how best to shrink your log size without breaking your log shipping integrity.



I apprecaite your help.

View 1 Replies View Related

SQL Server To Generate XML Instead Of Redendant Query..file Open During Write Problem Or Job Not Running...

Jul 23, 2005

Here's a challenge that is killing me:I've got 2 web servers and a SQL Server and about 5,000 'users' whostay connected to the site all day. I have a page that is supposed tobe 'real-time', so to do this, I have a 1px frame that refreshes every15 seconds (so the other frame doesn't have to reload all the time--thetop only reloads when a new record or a changed record hits the db).The real time data can be filtered in about 8 different ways.Currently, I have each user querying a table that contains 1 record,including the max ID and the most current insert/update posting date.The browser contains a cookie with that date. When the browser receivesnotification that there is some new info on the server, it refreshesthe top page and reloads the data. This is happening for all users. So,I thought to eliminate the 5,000 users running the same (or closevariations) of the same query each time a records is inserted/updated,that I would generate an XML file with the current day's data.In a dev environment this works 'ok'. I'm doing this by running anActiveX job on the SQL Server that calls a stored proc (FOR XML) andwrites the content to a file. Then from the web servers, I'm queryingthis file for the new timestamp and then if newer than the cookie,grabbing the XML (using the httprequest in the ASP XMLDOM) and usingXSLT to transform the data instead of parameterizing the queries.Theoretically I love this solution. Problems happen in a LIVEenvironment where either the file is being written to or the job isn'table to run. When 2 records are trying to be written within the samesecond, the file isn't being written (or maybe that the http requestingthe XML is keeping the file locked?)....anyway...this is a HUGE problethat I can't seem to solve. Once we roll to .NET I think storing thedataset in cache and updating cache (still don't know how I'll triggerthat without each user checking the db)....Long winded, sorry...help?

View 8 Replies View Related

DBCC Shrinkfile Not Shrinking Data File

Jan 31, 2002

On a SQL Server 7.0 database I support, I've been unsuccessful trying to shrink a data file using dbcc shrinkfile (datafile_logical_name, 0). This worked fine for shrinking the log, but of the 4 datafiles that were created 2 shrank successfully and 2 remain unchanged. Unless the information on the General Tab on Enterprise Manager is incorrect, of the 15000MB allocated for one of the files, only 700 MB are used.

View 1 Replies View Related

Shrinking Unreasonably Large Transaction Log File.

Oct 12, 2002

Gurus,

I have inherited a SQL 2000 database ( (I am new to SQL DBA) and I found this when I was checking the db properites . The transaction log has grown bigger than the actual data file, I thought transaction log backups would truncate the inactive portion of the log file and shrink the transaction log, but it was not the case it seems, may be it was truncating the inactive portion of the log, but not shrinking it. This site does not have a job for truncating the data/log files periodically. What is the best method to deal this situation, how can I shrink the Transaction log quickly?,

All your suggestions are welcome.

TIA,
-Jay

View 2 Replies View Related

SQL 2012 :: VLF And Log File Shrinking With Logshipping Configuration?

Aug 23, 2015

We are using Sql server 2012. One of our production database has hight no of vlfs. We are planning to shrink the logfile to reduce the no of VLFs but the database is configured for logshipping.

What is the effect of logshipping when you shrink the logfile?

View 3 Replies View Related

Shrinking Database File After Removing Data

Apr 20, 2007

Hi there,

I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.

I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.

Kind Regards

View 2 Replies View Related

Shrinking Mdf File After Implementing New Filegroups - SS2005

Feb 18, 2008

Hi!
I have a database where I add some new Filegroups and moved some Gb's of
tables and indexes to new file groups.
Now I would like to shrink the source of these tables/indexes: the mdf file.
This means there should be much empty space now in the mdf file.

I tried to use dbcc shrinkfile, but it run for more than one day.
Are there some good strategies about shrinking the mdf file?
I had to restart the instanse because got problem using the db while dbcc runned...

Thank you for help

View 2 Replies View Related

Session Increasing Indefinitely

Aug 5, 2015

I`m using IIS7..At present, our company has come not a difference indeyo of operating the server using Classic ASP recently we met a big problem : session is increases indefinitely. 503.3 - ASP.net has a queue is full. The phrase occurs continuously. Is there any solution for check problems? or how can i see what source code page makes the problem (ex. 123.asp : session 300. 234.asp : session 4000).

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

SQL 2012 :: Reclaim Disk Space By Shrinking Log File After TLOG Backup

Feb 25, 2015

I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.

I tried doing a transaction log backup but there is not enough space on the Disk.

How can I reclaim this log space in SQL Server 2012?

View 6 Replies View Related

Identity Column Jumps Indefinitely

Feb 3, 2006

Guys,

Iam new to this forum, Hello to all...
Iam facing a problem in my application. Have recently noticed that my primary key column which is an " identity " with increment 1 being set.
But now iam noticing a various jumps in the number instead of 1. The numbers in the jump is not consistent.
Has anyone faced this kinda problem.
?????

View 4 Replies View Related

Script Task, Debugger Stops And Waits Indefinitely

Jul 7, 2006

Hello,

I have MS SQL Server 2005 with SP1 installed, version 9.0.2047
I am trying to debug a Script Task in SSIS.
I have break point on the first line of the code.
SSIS runs and eventually launches MS Visual Studio for Applications.
Line with the break point is highlighted in yellow.
After that Visual Studio is frozen. F5, F11 or any other key press produces a popup which says the following:

Delay notification.
Microsoft Visual Studio for Applications is waiting for an operation to complete. If you regularly encounter this delay during normal usage please report this problem to Microsoft. Please include a description of the work you were doing in Microsoft Visual Studio for Applications and when possible instructions how to reproduce this delay.
If Microsoft Visual Studio for Applications is waiting on another application you can switch to that application now, or you can continue waiting for this operation to complete.

The popup has to buttons: [Switch to€¦] and [Continue Waiting]
None of the buttons allows to proceed.

Any idea what causes Microsoft Visual Studio for Applications to a complete halt?

Regards,
Yitzhak

View 6 Replies View Related

How To Kill A Long Running Query Running On A Background Thread.

Sep 1, 2006


If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?

The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.

Is there a way to do this?

Thanks


View 1 Replies View Related

Mdf File Become Smaller And Smaller After Shrinking Db

Jul 20, 2005

Hi all:I restored one backup database (7.9 GB mdf) on two diffrent servers. Ishrunk them by clicking "Move pages to beginning of file beforeshrinking".After shrinking, one mdf file is 6.7 GB, and the other is 4.2GB. Ishunk again and again:1. the 6.7GB become 5.9GB, 5.2GB, 4.7GB and 4.2Gb (four times)2. the 4.2Gb become 4.0GB (just one more time)It is wired, I am wondering the mdf will be smaller and smaller if Icontinue to shrink them? What is the reason?ThanksWJ

View 1 Replies View Related







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