SQL 2012 :: Argument Daily Transaction Log Shrink?

Jul 7, 2015

For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.

She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:

- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.

- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.

- It looks unprofessional.

These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.

Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go

Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO

My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?

View 2 Replies


ADVERTISEMENT

SQL 2012 :: High Availability Group Transaction Log Shrink

Feb 3, 2015

I have a Customer running a database in a High Availability Group and I am not familiar with the set up... They have a transaction log that quadrupled in size during a data import and update which was generated by an external application. They have limited server space and would like to shrink the log again now as this import / update only happens once a year. The way this has always been dealt with in the past was by shrinking the DB and logs after the update.

Now however, when attempting to do a log or db shrink, an error message is generated which says that the log cannot be shrunk as the DB is in use as part of an Availability Group....

The more I search and try to read up on this subject, it looks like the DB has to be removed from the Availability Group before the log can be shrunk and then the Availability Group has to be re-created or restored in some way. Is there a simple solution to this conundrum?

View 9 Replies View Related

How To Decrease The Size Of The Transaction Log Daily?

Jan 23, 2008





I have a database that the transaction log grows about 1 GB per day. I would like this size was decreased daily. Does anyone have any suggestions?
Some friends told me that after the Full backup that is done daily, I should perform a backup of transanction log with option to truncate and after, make a shrink in the database. That is exactly what should be done?

Thank you,

Best Regards,

Ralph Haddad

View 22 Replies View Related

Argument Data Type Varchar Is Invalid For Argument 3 Of Convert Function

Jan 25, 2013

Where did i do wrong in conversion

original query
dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate)

I tried to use convert(varchar(50),Datediff,21)

Below is the exact code..

convert(varchar(50),dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate),21)

View 10 Replies View Related

Argument Data Type Text Is Invalid For Argument 1 Of Replace Function.

May 14, 2008



Hi There,

Could someone please tell me why I am getting the above error on this code:

select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit


serviceType (text,null)

Thanks,
Rhonda

View 1 Replies View Related

Daily Reporting Of Database And Its Transaction Log Files.

Apr 19, 2004

Hello -

I have a database server with over 300 databases. I want that MS-SQL Server should daily report me the sizes of SQL databases along with Transaction log files by sending me an email on my address.

How can I do that. Does someone have any script which can help me to do that.

Any help will be appreciated.

Kind Regards,

Rubal Jain

View 11 Replies View Related

Getting 10 Shop Of Daily Sales Transaction By Use Of Different IP Address?

Oct 28, 2014

We have 9 shop installed POS system and how to get the 10 shop of daily sales transaction by use of different shop IP Address? I don't want to repeat typing 9 time for change IP address as well.

select * from xsoheader inner join
xsodetail.memonum = xsoheader.memonum where xshopcode='%00*'

View 8 Replies View Related

Shrink Transaction Log

Jan 18, 2001

Is it possible to shrink Transaction Log file? If yes, how? Urgent matter please.

Regards,

View 1 Replies View Related

Shrink SQL 6.5 Transaction Log

Mar 7, 2001

Is it possible to shrink the size of a transaction log file in SQL 6.5. Normally in SQL 7.0 I use detach and reattach but this is not supported in 6.5. The log file for a 200Mb database is 749Mb with no unused space and no open transaction. Thanks

View 2 Replies View Related

Cannot Shrink Transaction Log

Sep 25, 2002

Hi,

I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.

Does anyone have a solution to this dilemma?

Thank You

View 4 Replies View Related

Shrink Transaction Log

Sep 26, 2007

How can I reduce the size of transaction log of a database...?pls hel me...

View 2 Replies View Related

Cannot Shrink The Transaction Log

Jul 20, 2005

Hi Friends,I have tried almost everything but I cant seem to shrink thetransaction log.Executing DBCC SQLPERF(LOGSPACE)gives me this info:Database Log Size (MB) Log Space Used (%) StatusMY_eems 368.49219 16.034182 0I made a complete backup of the database and transaction log and thenexecuted this statement:DBCC SHRINKFILE (MYeems_log, 1)and this is the message I gotCannot shrink log file 2 (ghgeems_Log) because all logical log filesare in use.DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages------ ------ ----------- ----------- ----------- --------------17 2 42880 34561 42880 34560(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.How can I get the transaction log to its minimum size?Any help will be appreciated,ThanksA.B

View 5 Replies View Related

Transaction Log Will Not Shrink

Mar 12, 2008



I have 4 large sql dbs. All 4 have been trimmed down by deleted recs from the db, then shrinking. 3 out of the 4 shrunk successfully and the log files are much smaller than the data files. On the 4th the data file shrunk to 14G, but the log file stayed at 47G. I have tried truncating and then shrinking, but it never gets any smaller.

Any suggestions on why this particular one will not shrink?

View 3 Replies View Related

SQL 7.0 Transaction Logs Won&#39;t Shrink!!!

Jun 5, 2002

I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated.

Here's my scenario:

Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication)

Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything)

My questions are:

1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode?

2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0?

It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-(

Christine

View 1 Replies View Related

Transaction Log Doesn&#39;t Shrink

Jul 31, 2000

Hi Everyone,
I have a SQLServer 7.0 conversion db. Data file is 89 mg. The log has grown into 238 mg. Db and log are backuped nightly. Optimization and integrity jobs run weekly. The transaction log keeps growing! Truncate log on checkpoint is on and autoshrink is on.
What else can I do to truncate my log?

LT

View 3 Replies View Related

Can&#39;t Shrink Transaction Log From 30GB To 1GB

Jun 1, 2000

We have a user database that was initially allocated 90GB for data file, 30GB for transaction log file. We decided to shrink the data file to be 2GB, log file to be 1GB using 'DBCC SHRINKFILE'. The result of data file is 2GB as we expected. But log file is 5,764 MB no matter how many times we shrink it or run 'backup log with truncateonly' against it. Can this be something to do with how transaction log allocate its segments called virtual log files? Is 5GB the smallest unit of log segment that can be shrunked? Is there any way around this? We are in a production enviroment, and this is pretty urgent.
Thanks in advance.

Tung

View 1 Replies View Related

SQL Server 7 Transaction Log - Can&#39;t Shrink!

Jun 10, 1999

For some reason (I'm sure it was my code) my transaction log segments grew to a huge size (>1 GB each). They are normally in the area of 150 MB each, so I wanted to shrink them back down. But I have one transaction log segment that won't shrink! I'm a relatively new administrator, but I've tried shrinking the database (which worked for all but one segment) and truncating the logs, I've tried issuing a CHECKPOINT, I've tried stopping and restarting SQL Server, but I can't get that last segment to shrink to a normal size. Any ideas?

Thanks,

Robin

View 2 Replies View Related

Shrink Transaction Log After Replication

Sep 9, 2005

Hi!

View 1 Replies View Related

Transaction Log Backup Did Not Shrink Ldf - Help Please

Aug 15, 2007



I am a bit confused. I was told that backing up the TLog cleared out all the commited transactions which shrinks the log. Is this true, becuase I started with a 72 gig transaction log and the created a maint plan to back it up as a .trn. After this was successful the 72gig .ldf was still the same size. What am I doing wrong to get this log size down?

Thanks!

View 6 Replies View Related

SQL Server 2012 :: Stored Procedure Argument For Select Where Value IN Statement

Feb 17, 2014

I have a stored procedure that ends with

Select columnname from tablename order by ordercolumn

We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.

The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:

Select @userCount = COUNT(*) from permissions where UserID = @userID and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (dbo.sp_FoldersOfFile(@fileID))

The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.

View 9 Replies View Related

Integration Services :: SSIS 2012 - ARGUMENT Has Mismatched Quotes

Sep 16, 2015

I am using file path as package parameter in the SSIS 2012 package. This parameter has file path value which has spaces as there are spaces in some of the folders. When I execute the package from the SQL Agent job, I experience the following error:

Executed as user: xxxxx Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    The argument ""p_test";""xxxxxxx.comfilesCommon2015
"TEST" "Event" "ZONEDataCust" FilesOut" /Par "$ServerOption::LOGGING_LEVEL(Int16)";1 /Par "$ServerOption::SYNCHRONIZED(Boolean)";True /CALLERINFO "SQLAGENT" /REPORTING "E" "
has mismatched quotes.  The command line parameters are invalid.  The step failed.

Parameter name: p_test
Parameter value: xxxxxxx.comfilesCommon2015 TEST Event ZONEDataCust FilesOut

I am specifying this value in the sql agent job. The package is working fine locally with the same parameter value. I have tried using double quotes for the entire path but it did not work out. How can I get this resolved?

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

Mirroring :: How To Shrink Transaction Log On Mirrored DB

Oct 7, 2015

Today, I became an involuntary DBA until my company finds someone else. We have an SQL R2 DB that is mirrored that failed dur to the transaction log growing and filling up the disk. As a band aid remedy, I was able to add 20GB to the disk (it is a VM) and then I backed up the Transaction log and did select log_reuse_wait_desc from sys.databases where name = 'mydb' and the result showed that the backup was successful (NOTHING, I think was the result).

After this I was able to resume the mirror and everything has been running fine. What I need to do now is to shrink the Transaction log so that if it starts to grow again, I will get an alert and can avoid today's issue. From what I have read, I can just use DBCC SHRINKFILE [logname] after I do a backup and the changes will be reflected on the mirror as well. I have shrunk the T-log in non-mirrored servers before, just don't know if there are any key differences.

View 6 Replies View Related

Truncate Transaction Log And SHRINK DATABASE

Aug 28, 2007

Is it possible to truncate Transaction Log and Shrink DATABASE while the database is being used by users or the database becomes unuvailable during this operations?

Thank you.

View 2 Replies View Related

Switched To Simple Recovery Model When Will The Transaction Log Shrink?

Jul 23, 2005

My trancaction log is 25GB and my database file is 39GB. I justswitched to the 'Simple' recovery model from the 'Full' recovery model.When if ever can I expect the size of the transaction log to reduce insize? Is there anything else that I should do to aide with thereduction?Thanks,Peter

View 5 Replies View Related

Mirroring :: How To Truncate / Shrink Transaction Log If Mirror Is Enabled

Aug 18, 2015

We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.

We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.

How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.

View 17 Replies View Related

SQL Server 2012 :: Daily Sum Aggregation

Jul 14, 2015

I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it.

SELECT
Receipt
,"Date"
,Item
,Reason

[Code] ....

View 3 Replies View Related

SQL Server 2008 :: Unable To Shrink Transaction Log - Could Not Locate Files

Jan 10, 2012

I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:

use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)

I get the following message:

Msg 8985, Level 16, State 1, Line 1

Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.

I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.

I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.

I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.

View 9 Replies View Related

Transaction Log Does Not Truncate And Shrink Using A SQL Server 2005 Maintenance Plan

May 15, 2006

We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.

View 29 Replies View Related

SQL 2012 :: Copy A Database To Another Instance Daily

Jun 26, 2014

Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target. We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do. Permissions are different on each instance, so would prefer not to overlay users on destination database. Options we are looking at right now are...

Log Shipping
Snapshot Replication
Transfer SQL Objects Task (SSIS)

Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.

View 5 Replies View Related

SQL Server 2012 :: Daily Maximum Sold - Per Day

Jul 14, 2015

I have a code I wrote and I'm required to get Product Name and Quantity with the maximum quantity sold per day. My issue is with the "per day" part, here's my code below.

SELECT
t.SalesOrder
,t.OrderQuantity
,t.OrderTotal
,t.OrderDate
,p.ProductName

FROM Transactions t
INNER JOIN SalesPerson sp
ON t.SalesPersonID = sp.SalesPersonID
INNER JOIN Product p
ON t.ProductID = p.ProductID

WHERE sp.SalesPersonName LIKE 'John%'

Now from this code I need to get only Product Name and Quantity with the maximum quantity sold per day, here's what I tried;

SELECT
SUM(t.OrderQuantity) AS Order_Quantity
,p.ProductName

FROM Transactions t
INNER JOIN Product p
ON t.ProductID = p.ProductID

WHERE sp.SalesPersonName LIKE 'John%'
GROUP BY p.ProductName, t.OrderQuantity
HAVING MAX(t.OrderQuantity)

View 2 Replies View Related

SQL 2012 :: How To Schedule Daily Backup At 6:00am

Jul 29, 2015

I need to schedule daily backup at 6:00am. how to do this. I'm on Sql Server 2008r2

View 1 Replies View Related

Transact SQL :: Cannot Perform Shrink File Operation Inside User Transaction

Sep 18, 2015

I am getting an error about "Cannot perform a shrinkfile operation inside a user transaction", but I don't have a shrinkfile command in my procedure.  Does SQL hang on to that command if it was received earlier in a different procedure?

View 5 Replies View Related







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